In defense of the VBA developer

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:

  1. I use and have contributed to a tool that modernized the ALT+F11 VBA Editor.
  2. I have used and contributed to a tool that lets you make REST calls from VBA.
  3. 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.

One thought on “In defense of the VBA developer”

  1. Very nice blogpost. I appreciate you keeping the context always in focus.

    These tools are just tools. As someone said, if you’re trying to hang a picture, you don’t need the right hammer, you need a hole in the wall. With enough time and motivation, a person could scratch a hole in a wall with a sewing needle.

    If a person is in a silo and the hammers are too hard to acquire from another silo, then you do what you gotta do. The context is EVERYTHING!

    But let’s not view Excel as “better than nothing.” There are plenty of times when Excel is indeed THE tool to use. It hurt me to see someone write 6 hours of JavaScript code to do something that Excel is built to do. She could have had that done in less than 10 minutes. Alas, in her world–the world of the professional developer–Excel is the devil. So, she wasted 5 hours and 50 minutes of her life on something that wasn’t restricted by a silo or having to wait for a DBA to get a report for her. In this case, it was just an attitude and perception that Excel isn’t a real development platform.

    Liked by 1 person

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s