All valid points, but I think the biggest reason is:
- It's what's available.
There is a bit baked into this statement which the article breaks down further:
- Companies won't approve anything else in the hands of ordinary users
- Companies' developers are too busy with too high priority items
And some things not mentioned in the article are also baked in:
- Even when developers get around to a project that could replace VBA, they don't understand the project, underestimate the time and resources required, and deliver a subpar product as a result
- Companies lay off people doing work IT and developers can't be bothered to support with no real plan other than overburdening the remaining ordinary users with extraordinary problems
VBA serves an awesome niche. I once built an awesome simulator that did some pretty complex optimization stuff. The main sheet had input cells for the user, a couple of radio buttons for toggling certain features, and a button to fire off the built-in Excel solver plugin and pull certain values from that process and display it all on a GUI on the first sheet. It took me just a couple of days despite zero VBA experience and most importantly I could send it to all of our customers who then had a full simulator that they could play with alongside their engineers. They didn't have to install anything (just click a button within excel to add a plug-in). Simple simple.
One of the bigger things I've ever built was a massive set of tooling based upon Excel + VBA + proprietary API. The old days ('00s). The best thing of developing in VBA was that the API was properly documented. Any function had documentation via VBA, plus via the primary tooling, plus via big old books. On top of that I had a premium support line to the developers of the API as I seemingly was one of the few worldwide actually using it. Heck, even a professor that showed up in the documentation was kind enough to help me for a bit. A shitty, but high-paid job for 10 FTE was reduced to a one-person show where the main job was adding intellect, not pay-for-clicks. Probably still is a one-person show. It's the one thing Python and R never achieved for me (note - I am lowly skiled at his): object oriented programming that helped, not hindered.
I read a stat somewhere that there are at least an order of magnitude more Excel "programmers" in the world than all pro developers in other languages combined.
And looking at how much work is involved in even just setting up a JavaScript frontend, I’m pretty sure the Excel programmers are yet another order of magnitude more productive than the latter.
I agree. I worked as an analyst where due to security policies we only had VBA. There was application which consisted of an Access DB for a "front end" (containing an interface to input data, generate reports in ppt/excel, etc) which connected to another access database as the "back end" and an admin console to pull in data from a SQL database. I had a lot of fun maintaining it.
Yeah I know MS Access & Co. have kind of a bad rap, but if I look at what we were able to actually achieve for the business/users with those tools, it’s really painful to see how little progress we have made in the last 20 years or so.
> button to fire off the built-in Excel solver plugin and pull certain values
That's pretty cool.
VBA is terrific for glue code. Back in the day, before the Internet opened up the security hellmouth, ActiveX was pretty great for use cases like yours.
Early '90s, I made an in-house cost estimation app using Access 2000. It'd extract data from our MicroStation (belch!) CAD drawings to generate budgets and bill of materials. Huge time saver.
Cost estimation apps rely on a database of SKUs, assemblies, etc. Every entry can have dependencies, equations, etc. Like "for every 10ft of X, add 1 widget Y".
Super easy to implement with dynamic languages like LISP, where data can be code (macros). Not something Visual Basic is known for. My "one cool trick" was using VBA's built-in "eval" function equivalent.
In the late 90s I worked for a small network solutions company. Cable infrastructure, lan, wan, email/file/printer servers. Most of our work was campus sized networks. Military bases, hospitals, corporate campuses, etc. We did all our drawings in Visio which hadn't yet been purchased by Microsoft. Visio added VBA support around 97 or 98. As soon as they did myself and the other network engineer on staff, we didn't have any developers, wrote a similar system in our "spare time" to extract a bill of materials and generate a cost estimate. Included everything down to the number of rack screws.
I ended up leaving about 3 months after it was done but they continued to use it for a few years until it was replaced by a COTS system.
What a small world. My wife is a MicroStation guru (civil engineer), but not a programmer. I've often wondered about how to make CAD work more productive.
All true, but the fascinating thing about the article is that it's the Subject Matter Experts demanding to use something other than VBA to be more productive, and the IT people saying no VBA is the only thing allowed because...reasons.
Often those SMEs are data folks, and if they are allowed to develop in $TOOL_DU_JOUR, and that app becomes business critical, the IT folks will be stuck supporting it. And if that thing doesn't become well supported, or it isn't easy to find someone who can support it, that's a problem.
I've run into this quite a bit at my workplace. Some business group writes an app in Excel using VBA + an add-in and it becomes the core part of some workflow. But IT didn't know about it, nor did they know about the (for example) 32-bit ancient Excel add-in that it requires, which then breaks when an Office upgrade happens...
Now IT is stuck where a routine upgrade broke some weird edge case thing and needs to maintain a downlevel version of Office for a small group until they can re-develop their business-critical tool in something else.
Use-known-stuff rules up front -- in this case which may well be VBA -- alleviate a lot of these long-term problems.
In our case (and in the article), we wanted to collaborate with IT, code along-side them in a technology of their choosing. However IT explicitely told us that they would NOT allow us to do any coding whatsoever.
I, as an SME, am fully happy coding in C#, Java, Rust, whatever! As long as the language is turin complete, is pro-code and versatile enough, I'm all ready to go.
Do note that IT actively chose to develop the solution in Microsoft PowerApps, despite my advising that the solution would be better suited as a web app.
That's a great way to go, but unfortunately the reality is often the folks go off and write stuff in the non-supported way. The business gets dependent on it, and then years later -- because IT is there to support the company overall -- IT gets stuck supporting it even if long ago they said they wouldn't.
Sure, you can have an internal political fight, but it only goes so far when everyone there is supposed to be working for the company. So while there'll be strong incentive to move to something else, there's still a need to keep it working in the mean time.
If you can prevent this up front it's better all around.
I've been on both sides of that kind of situation, and IMO when an IT department gets that calcified, it's a sign that their days (or the company's days) are numbered. Either the non-IT staff will get fed up and replace IT, or the non-IT staff won't be able to get their work done efficiently and the company will fail.
The non-IT staff build those things because they've identified a way for the company to improve itself, but the process for getting what they want from IT is too expensive/onerous, IT has delivered disappointing results too many times, etc. Find a way to meet in the middle, or the non-IT staff are just going to build them in their own shadow cloud account and eventually make the IT department redundant.
There are tons of incredibly beneficial computing improvements that it doesn't make sense to spend IT resources on, because there are better ROI opportunities for them to focus on.
But! That doesn't mean the things they can't handle aren't valuable.
My preferred method is (1) require documentation (using a standard template) on all processes implemented by non-IT (what it does, how it does it, what value it delivers to the company, what the fallback manual process is, etc.), (2) store these process docs in a centralized location, which then becomes IT's backlog, and (3) any change control / regulatory requirements.
The grand bargain is then:
- Anyone is authorized to improve processes, if they generate the documentation
- IT has the authority to force decommissioned of an existing solution *after* they've delivered a working replacement
That seems to align everyone's incentives more clearly on "the good of the company."
True, but that's kind of like Twitter having to go back and rewrite their Ruby services in Scala down the line.
Getting a viable Minimum Viable Product is all important. If a non-developer can hack that together in Excel + VBA, more power to them.
Going back and rewriting it in a Proper Programming Language after the fact is an acceptable cost, once you have something solving an actual business need.
The issue I've most seen is that it never gets rewritten because the business side sees it as sufficient and just uses it. The techy/IT/programming folks never even hear about it until it breaks.
Most of these things are sheets which perform perfectly fine as-is, with their issues being around long-term maintenance. (Routine platform upgrade break the app, but the platform owners had no idea about the app until it broke for the users. The app didn't really even have an owner anymore because IT was never involved to assign it an IT owner and the author is long gone...)
Yes, it's the old-as-time problem of misaligned interests, but it's the reality in most corporate/enterprise IT and is a strong reason for prohibitions that may seem stubborn to devs.
- It allows a user to easily extend their current work environment (i.e. MS Office applications). Other languages and IDEs could do that, too, but not so easily. This allows users to somewhat more gracefully extend their documents/data and knowledge instead of starting all over again with a "proper" programming language/environment.
- I would not call it fast or resource-efficient, but fast enough and efficient enough for most unsophisticated purposes.
- No red tape for installation, IT cannot (easily) disable it, isn't an extra line item on any bill
It’s fast and resource-efficient in that it doesn’t need to start up a comprehensive extra runtime environment like the CLR, and doesn’t need double the memory like GC languages tend to do, and is faster than many interpreted languages due to its P-code and its tight native integration, not needing an additional interop layer to communicate with its environment.
But unfortunately microsoft didn’t invest into VBA in 20 years, other than keeping the light on. And it lacks so many modern features.
There was an attempt at a .net version of VBA (would have worked the same way, with a mini visual studio embedded in Office), called VSTA. But it was killed. So the cattle (business users) is stuck with 1990s technology.
It's a hindrance to -us- but I suspect for code that's designed to be passed from SME to SME that -not- having access to features so you have to write things the ugly stupid way ... may actually be an advantage, since the next SME along only has to have a sufficient tolerance for 'ugly' rather than an understanding of the features you and I would both want.
Is it still something you can code with your mouse?
I remember having to deal with it on my first job because the team knew nothing about coding. They had “recorded” macros by clicking around and never seeing a line of code. It was incredibly brittle: any change to the table, even adding a comment, would break it, but it allowed them to automate a task.
This sort of programming is very powerful. It lets people who have no idea what big O is or how to make a fully automated CI/CD system with testing and all of the bells and whistles just get something done. Just to make a dialog and automate something. That type of programing is very powerful. Many times a huge mess to clean up but very powerful and gets things done. We sometimes lose sight of that. Computers are to get things done. Getting rid of the moat we have build could be very powerful.
In an ideal world this is how first draft of software would be done. And professional software engineers only come in when it's time to make it secure, fast, less brittle, scalable, available to more users, etc.
Like finding the screen that takes forever to load because there's a hidden O(n^2) in there and replacing it with an O(n log n), etc.
We used some weird testing tool from HP I think. It could record you clicking around in an application and generate a VBScript. You could then go in a modify the generated code, add parameters and fix the brittleness. It meant that you could get a ton of coding done in a very short about of time.
People struggle with not knowing how to describe a task they can do but not with code. The record gets you very close very quickly. If you’re fluent in adjusting selection logic you’re usually going to have it pretty easy.
I feel a strong urge to suggest that it may in fact stem primarily from a deliberate and maniacal worship of Nurgle, the Chaos God of despair, disease, and destruction.
– The IDE is built in.
– The syntax is beginner-friendly.
– It’s stable and doesn’t change every six month.
– It’s well-documented.
– No build steps, it just runs, and fast.
– It’s resource-efficient (CPU, RAM).
– You can easily create dialogs and forms using the built-in visual GUI builder.
– You can break into the built-in debugger from your Office document.
– If you want to get fancy, it has interfaces and classes.
– You can call any win32 function and use any COM object.