Why Do People Still Use VBA?
Sancarn (via Hacker News):
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.
[…]
Looks like the only automation platforms which can connect to all the data sources we need is VBA and Powershell. Power BI Desktop has been introduced in our business but doesn’t hit all the platforms which VBA does, and even if it did Power BI cannot be used for process automation where-as VBA can, so what’s the point making the switch? Users who do use Power BI to target these other datasets usually generate CSVs of this other data and store these in cloud sharepoint system, but what generates those CSVs? VBA.
[…]
Now, we’d love to use a higher level language in our organisation to handle this business automation. However, every request for a high level language to be installed across the team/business e.g. Python / Ruby / Node / Rust etc. has been rejected by CyberSecurity in favour of technologies like PowerAutomate, PowerApps which as you can see above barely touch any of the data we need. It is supposedly “Against the technology strategic vision of the company” to allow “end-users” access to high level programming languages.
I’ve been surprised to see many pro devs using Excel/VBA as a secondary tool.
One example: a couple years ago I was working with a big hedge fund and one of their data analysts sent me an Excel model he had built and I was tickled to see the .xlsm extension (i.e., VBA code on board).
[…]
He said something that stuck with me, “Excel makes it easy to understand the dependency structure that is implied by computations. If I had done this in Python, I’d be answering questions about it all day long.”
Update (2023-11-20): randmbits:
I used to be a VBA wizard and it was because it was the only thing the org didn’t regulate away. It took forever to get approval to use it too. We had a party when they eventually allowed us to use SQL.
5 Comments RSS · Twitter · Mastodon
"Excel makes it easy to understand the dependency structure that is implied by computations"
Feels so true. I had never really thought about it this way, but this is why I still do some "programming" in Excel (not VBA, but sometimes fairly complex data manipulations). There is of course something very effective about the space organization of the code and logic in a 2D array (in fact 3D when you include multiple sheets corresponding to successive steps).
Even though I have 20+ years experience in "serious" programming (compiled languages and scripting languages, frontend, backend, apps, etc.) and I do write "real" code for many other tasks where data needs crunching.
Come to think of it, it would be nice to mix the worksheet concept with "real" programming languages. (Maybe it exists, but somehow has not taken over the world... yet?).
Excel and VBA are probably the best inventions since sliced bread, without it, I would probably be a migrant farmer in California ;-) Anyone who doesn’t see its value probably doesn’t have any experience in a regular office environment.
I have years of experience in compiled languages such as C/ObjectiveC, and numerous interpreted ones (vba, of course, python, perl, bash), and VBA just gets the job done in an office environment that depends on MS Office apps (and other by proxy) while it is not very demanding on IT.
@Charles
One of the goals of Objective-S is to integrate dataflow constraints (the core idea in spreadsheet computation) into the language naturally and flexibly...so you have the tools to build and adapt your own constraint systems and plug them into the language.
It already works quite well for a lot of cases.
There is syntax: |= for one way dataflow constraints, =|= for two-way dataflow constraints.
1st class references, composable stores and polymorphic write streams provide the elements that let you easily construct a store that can maintain dependencies.
@Marcel thanks for the insight :)
I feel like anytime someone asks "Why are people still using old technology X?", the answer is either "Because newer technologies don't do everything X could" or Institutional inertia" (or both).