Yesterday I did what I was put on this earth to do, get into an argument on the internet. I defended VBA.
. . .
Well with humility I present those thoughts here. However, before I present those thoughts I need to make three confessions:
- I use and have contributed to a tool that modernized the ALT+F11 VBA Editor.
- I have used and contributed to a tool that lets you make REST calls from VBA.
- I have written code to facilitate low level debugging of VBA with Sysinternals Process Monitor (procmon.exe).
Just to be clear, I am by no means a full time VBA programmer, I’ve simply dabbled extensively. In terms of hours logged or LoC its quite clear this is not a major endeavor of mine.
Ok my biases are reported. Full speed ahead with the apologia.
Sometimes Excel is the right tool for the job. There are many reasons for this.
Sometimes its a technical reason. For example, QuantLib is a really great quantitative library with binding for many languages such as C, C++ and Python. QuantLibXL is the excel binding for them. Excel is great at doing math and accounting. Quantlib just takes that to the next level with quantitative finance. It’s an obvious organic extension.
Sometimes you just need a little VBA. For example, in my sysadmin days I’d often be given spreadsheets of servers for audits. A little VBA would allow me to do reverse DNS lookups on the IP address columns in these spreadsheets. Whatever sin there is in adding VBA was a small one, and it would not be practical to not use Excel for the task because it wasn’t my decision.
Sometimes Excel is just being used to prototype a front end. Sometimes a developer that doesn’t want to write a web front end would rather use VBA-Web to write a quick API for an analyst for some REST services. This allows the analyst to own the front end and extend it, while the developer can do what they are good at. I’ve been in this situation. XL allowed me to be more of a back end programmer and not a designer.
Finally sometimes you’re in a silo in a large org with locked down desktops and cannot get Visual Studio, Python or R installed on your machine. However, you have office and VBA isn’t locked down. That decision to not support all those development environments is probably the right decision for the overall health of the org. A few smart people at the top lock everything down and they can scale out support issues with low salaried minimally trained help desk workers. Despite this your silo’s managers and developers allowing VBA abominations to grow beyond what makes sense within your silo. Its a crappy situation overall, but you have a silo doing the best they can to serve their larger organization even if the org cannot properly facilitate them. I’ve never personally been in this situation. However, I’ve interviewed at investment banks.. A former bos worked at an investment bank that used VBA extenively. I’ve had many a beer with sysadmins at investment banks. Its quite clear to me why both sides do what they do.
Is excel often not the right tool for the job? Absolutely! It has been described as the second best tool for any task. The actual truth is it the tool people have and know how to use that gets shoehorned into a lot of tasks that could better be solved by other tools. Often these tasks have deadlines that makes excel the right tool at the moment pragmatically speaking. Excel is kind of like WD-40. WD-40 is a great solvent, that has some lubricating properties. However, its usually not the lubrication you want to use. Its great in a pinch if you don’t have machine oil. Just realize it’s going to quickly evaporate away unlike machine oil.