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.
