Why do people use VBA? In order to answer this question, we must first look at another question – who actually uses VBA in the first place? In 2021 I ran a poll on /r/vba where I asked redditors why they code in VBA. From these data, we can clearly see that the majority of people who use VBA do so mainly because they have no other choice. Many organisations run their entire business processes with Excel, and when a little bit of automation is required VBA is usually #1 on the list.
Source: Why do people use VBA?
I was just ranting about this to my kids a couple days ago.
Even in large companies, with massive IT departments, and lots and lots of internal databases and information systems, US businesses are still run on Excel. That’s not subjective. I’ve worked for decades inside three Fortune 250’s (and a couple smaller shops), and bad Excel “applications” are in use at all of them. And after one person learns enough VBA to get a spreadsheet dealing with a particular issue to save a little time for themselves, they start sharing them with their colleagues, and the problem gets worse. Half of my career has been built on making “real” applications out of Excel spreadsheets that were wobbling under their own weight.
But why?
Back in the old days, IT grew out of the accounting department. They had the only computer in the building, and it was an IBM mainframe. Great stuff, right? Saved a lot of time and paperwork, right? Except that it didn’t. It quickly ossified the company’s work flow, and permanently hobbled its ability to adapt to change. It would take years to get any changes made in the mainframe group, and people were frustrated. Along came spreadsheets, and everything changed.
I saw it myself in my first engineering job in 1993. We got new computers with Windows 3.1 and Quattro Pro. (And AutoCAD. And, of course, on mine: DOOM!) After weeks of bugging the lady who ran the mainframe — who apparently had to write a whole program — I got her to dump the BOM for a couple of our products to compare for similarities. I downloaded the 2 files to my PC with a token ring mainframe interface card. I think they were only about 1MB each. With 8MB of RAM, I had twice as much memory as our System 36, and I could open both BOM’s in a spreadsheet, and analyze them to my heart’s content. Understanding that I had more processing power on my desk than the freezer-sized unit in the other room was eye-opening.
American manufacturing companies (at least) never got the message. The invention of the spreadsheet spared them from facing the fact that the mainframe had become the black hole of their IT world. As changes were becoming impossible to get from the mainframe group, PC’s with Windows and Excel allowed people at all levels and in all job functions to start working around the mainframe and its limitations.
Now, these kinds of companies are decades behind the curve. They thought “outsourcing” was going to fix all of their problems. When it didn’t, they thought “consultants” would be the trick. Surely “agile” will do it this time, right? No. It’s not the process; it’s the mainframe. Forcing every corporate workflow and piece of data to be kept canonically inside a 40-50-year-old legacy system’s limitations is quite literally killing the company. It’s certainly killing their competitive advantage.
My current company still breaks our primary software component into 8 pieces because that’s what would fit on floppies to send to the plant to program the hardware. Every IT system — and every spreadsheet — in the company has to deal with this 40-year-old legacy issue because that’s what we programmed the mainframe to expect, and now that’s the only way to bill a customer for it. So the logistics of dealing with multiple trees and branches of software (and multiple trees and branches of documentation about the software) is multiplied by a factor of 8 to this day. There is no escape from this black hole. You can’t re-engineer this situation. It’s too ingrained.
I worked for one group which, on every engineering release, had to get a giant table of software versions — each with their 8 part numbers — into the mainframe. The process was so onerous that they would spend days clicking through mainframe terminal emulator screens to get the information they needed, to make a spreadsheet in a particular format, which they would send to another group to actually enter back into the mainframe. Part of the problem was the spreadsheet had to be in 3 columns, but the mainframe screens were in 4 columns (or vice versa), so a lot of it was purely formatting. I wrote a little program to automate all of this, but I’ve left the group, and I’m sure no one uses it any more. The particularly stupid part of this story is that people fought me to write a little software that saved these people 10’s of hours a week in the name of their own job security.
And no one in the corporate hierarchy cares. In this day and age, the executives are all just playing the waiting game, letting things atrophy — saying all the right things publicly — while they wait until the financials are inverted enough to make the company a juicy prospect for a buyout in an industry-wide rollup by private equity.
Meanwhile, actual people have to get stuff done to stay employed and feed their families. Inside the company, the managers have to look at the three year lead times to get a simple application written by “corporate IT,” and can do nothing but just continue to throw bodies and VBA macros at it. Or, in my case, have me write something to do it. That is, until it gets successful enough that people notice, and it gets taken away from me, but that’s another story…