– 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.
- 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
Which is kinda sad actually.
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.
Here's a modern example:
https://softwareconnect.com/construction-management/planswif...
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.
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.
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.
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.
Otherwise, you're on your own.
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.
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
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.
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.
- 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
VBA seems like a solid choice compared to where they have their data being stored.
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.
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.
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.
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.
- embedded database via Access Data Objects (ADO).
There's still no modern equivalent. The ADO notion was lost in the transition from workgroup (file sharing) to client/server (ODBC).
ORMs, ActiveRecords, builders (eg JOOQ), templates, etc. are all partial solutions. Abstractions with sharp edges and traps.
(Yes, I'm working on it.)
Huh, fast? Sure, it's fast enough for its use cases, but it is not fast.