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.

It might be niche for HN, but in muggle world its enormously popular.

Heh, muggle world is the niche, but that niche is arguably bigger than all of the dedicated software engineer niches.

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 even getting a JavaScript frontend set up, I’m pretty sure the Excel programmers are yet another order of magnitude more productive than the latter.

Which is kinda sad actually.

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.

At that point you're abusing the word niche

Thus the "Heh".

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.

> 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.

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.

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 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

It seems like the bigger concern would be all the important data they have in Lotus Notes, as well as some of the other systems mentioned.

VBA seems like a solid choice compared to where they have their data being stored.

I think I just read the best reply ever

What would have happened if VBScript had won the DHTML wars?

We’d have VBTypeScript now. ;)

But typescript has "using" now? How can VBA still be so popular?

VBA supports automatic cleanup via Class_Terminate. :)

- 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.

> – No build steps, it just runs, and fast.

Huh, fast? Sure, it's fast enough for its use cases, but it is not fast.

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.

It’s also the ultimate discoverability tool.

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.

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.

Yes.

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.

You can still record VBA macros in MS Office.

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.

Macros recording a series of mouse clicks: That's what I did when I was a finance intern many moons ago. Knew nothing about programming at the time.

And it's absolutely baked into Microsoft Excel.

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 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.

Or just make it known that if you want support, here are the languages our IT staff know and can help you with.

Otherwise, you're on your own.

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.

To me, it's a pressure release/ROI question.

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.

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.

Yes and:

- 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.)

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.

Maybe that turned out to be more of a strength than a weakness?

A strength for whom? Not the end user.

It depends on the user. I use the basic VBA components to build my own features (exactly how I want them). I'm happy that Microsoft isn't meddling.

The lack of basic functionalities like generics, sort functions, lambdas, is a hinderance if you got a taste of .net.

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.

Sorting is fine in VBA. But, I admit, it'd be sweet to have lambdas.

Here’s another: - ChatGPT is well-trained on VBA.

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.