[LarryDarrell]

I rewrote a 3000 line VBA function into C# earlier this year for a Oil/Gas company. It was surprisingly easy. Sometimes tedious, but easy. It runs faster, it's easier to maintain and now they can slap whatever front-end they want on it.

Is it money, fear or gatekeeping that keeps these VBA programs untouchable?

[HeyLaughingBoy]

Probably all three.

I used to be in the Medical Devices field. A couple years ago, I was told to add a feature to a massive VBA app hosted in Excel that we (S/W development) inherited from another department. It was hands down the worst code I have ever seen. Everything was a global variable. I flat out told my boss that it barely worked, didn't come close to meeting the weakest of our development standards, and since it was used to disposition product (determine whether or not it was acceptable to be used on Patients) it was an FDA audit nightmare waiting to happen. I added the features, but made some pretty unpleasant commit comments since I didn't want to have my fingerprints all over it.

In all fairness, it was originally developed by a scientist with no SW dev training (I knew him well) for his own use, and just grew over the years as he was told to add more features to it. I have no idea how a company with that level of process strictness ever allowed this fiasco to happen.

[rant over]

I estimated the rewrite at 6-12 months. It was really not a very complex program and since we already had the Validation tests written and documented, we at least knew what tests its replacement would need to pass. The problem was all the stuff that we knew it was doing that wasn't in any of the tests. That would have been the majority of time spent to replace it.

Since I left, I heard ownership of it was transferred to Corporate IT and they were making it into a web app to run on their intranet. Heaven help those guys.

[antpls]

> Since I left, I heard ownership of it was transferred to Corporate IT and they were making it into a web app to run on their intranet. Heaven help those guys.

At my last job, I developed automated processes. Some software companies sell tools to automate clicks and keypresses. The idea is then to reuse that Excel macro no one understands inside a VM on a server, and adding a friendly webservice on top of it. Users of the web services don't know an actual screen is running and and an Excel file is being clicked for them.

[Maybestring]

It's simply that the people doing the development only get to use VBA. Not that having a chemical engineer teach themself C# over their lunch break is going to turn out great software...

Having worked at a big company as a mechanical engineer and produced a few crappy VBA apps, I did it because it would take significantly more time and effort to get approval for an IT project, than to learn VBA and do it myself.

[ender341341]

Most of them aren't maintained by technical people. Someone wrote some excel file years ago and every month it's copy pasted and the new data put in place of the old and it usually works until it doesn't.

[rdiddly]

For me it's time, but I guess that's just money...

[throwaway_12l4]

> Is it money, fear or gatekeeping that keeps these VBA programs untouchable?

I was involved in work with a regional financial institution. They had a suite of line-of-business apps written in Access and VBA.[0] This was in a pure support and maintenance mode. This series of apps was written over the course of a decade by one developer nee business user. It represented a literal Great Wall of Chesterton.[1] There was so much process and institutional knowledge tied into this suite and the original developer left after being absorbed into the IT organization.

Some fun observations from work that was done on this suite of applications:

- "We can tell where the original developer learned various programming techniques and when something was written based on that."

- There was a source control and deployment app written entirely in Access DBs and VBA

- There were two scheduling applications, one designed to replace the other, but the first never fully retired. These launched jobs based on dependencies and timings.

- The applications ran in Access 2010. No upgrade was possible due to observed breakages when attempting to change.

- A Windows security update caused an outage for a day.

- Some data was centralized into a SQL Server instance and Access was a thin app layer on these tables. Others held data in the Access database.

- Different apps could reach into and run arbitrary queries on data of other apps. This was sort of RPC. Huge spooky action at a distance.

- There is a ticking time bomb based on Access database size limits. They run compaction jobs on a regular basis, with increasing frequency. One of the core apps will simply stop working one day in the not so distant future when its database cannot be compacted to be smaller than the Jet engine limit of 2GB.

I mentioned that this thing was in perennial support and maintenance mode. There were several assessments over the years for a migration to .Net. All were eventually scuttled due to cost and fear of functionality shifts. No one knew if what was there was correct, and some of the bugs we fixed demonstrated that there was plenty that was incorrect, though scope and impact were difficult to measure. No one wanted to own a migration due to the likely fallout. Different would be wrong, even in the face of huge evidence to the contrary. A C-suite executive was ousted largely over this suite of applications. The Access lives on.

Eventually a migration, in part or whole, will be necessary. It will be expensive. It will be painful. Each day increases the expense and pain. But every day, it is cheaper and easier to run and maintain.

[0] This thing hit commercial and customer facing processes, internal things like HR, letter generation, asset management, and basic reporting, among other things.

[1] See: https://en.wikipedia.org/wiki/Wikipedia:Chesterton%27s_fence

Note: Throwaway because of the level of detail