Have you noticed that real (i.e. professional) developers (as opposed to hobbyists, like me) tend not to hold Excel VBA in much esteem?
I am generalising, but its rare to find such pro developer who regards VBA as a development platform that can stand shoulder to shoulder against the Javas, Pythons, JavaScripts etc. And they have their reasons for this, which I am not knocking or even challenging.
But VBA is just a tool, and, as with most tools, there are jobs that it does very well. I am going to discuss a few classes of activity that VBA is very good at:
Learning the basics of coding:
These days there are lots of beginner level coding platforms. From Scratch to the micro:bit JavaScript Block Editor, tons of languages have been tailored to take people on a learning journey that leads to coding competence.
Back in the pre-y2k days I can’t think of much besides Logo that served as a dedicated entry-level coding framework, and Logo was limited by having few onward journeys.
But with VBA, all you needed to do was undertake a few actions and record them as a macro, then you could check the code that had been generated. This ability to write code automatically and review and edit it straight after was ground-breaking at the time and an incredibly powerful tool for an enthusiastic learner, and it is a method I still use 20 years after first learning VBA.
For young kids I think its a given that Scratch is an easier vector to go from knowing nothing to having some coding competence, but if you are already a user of Excel and are keen to expand your capabilities or learn something interesting get recording some macros, peak at the code and make tweaks. Before you know it you’ll be using macros and VBA to enhance your spreadsheets. And the skills and knowledge you gain are transferrable to other platforms.
Rapid Application Development (RAD):
Some times a business needs a solution quickly, and there are many cases where managers care nothing about the tech that is used to come up with that solution. For many in the IT world time literally is money.
I have designed and built the exact same app in VBA and Java (to convert csv files to JSON) – suffice to say it took a third of the time in VBA.
One element of VBA which lends itself to RAD is the ease with which you can access functionality from other Office apps, e.g. using the Outlook application to send emails. It would take a competent VBA developer a morning to set up an app that would merge data from 2 csv files and output the data into emails which are sent by Outlook. To illustrate real world applications for VBA I’ve listed a lot of specific solutions that I have built in this article – many of these use functionality from other Office apps .
Combining the easy to visualise data tables (i.e. workbooks), the huge array of built-in functions and available reference libraries, the quick and intuitive form building and easy access to all Office functionality makes building complex VBA apps quick and easy for anyone familiar with the environment.
Whether the solutions generated by VBA are as robust or elegant as other platforms is irrelevant in many contexts – in general only developers care about the tech that underlies a solution.
Reliable environment:
A lot is said about write-once-run-anywhere, and I am not sure just how ubiquitous the latest options really are. Does Java live up to its promise in this regard, and are online apps a work-around where the target computers for an application have a very varied config?
I don’t know the answer, but I do know that if you have a network of machines all running Excel, then you have a stable and reliable platform that you can develop for (FYI handing different versions of Excel is trivial for a developer, and is greatly helped by the Function Tables that ensure every new version is backward compatible).
IDE:
The IDE (Integrated Design Environment) is not half bad. It lacks the sheer breadth of choice that something like Eclipse offers, and its not changed much since earlier incarnations, but its solid and easy to use. If you’ve ever worked in an IDE before you will find it intuitive.
More importantly, perhaps, is that it sits within every version of Excel… if you have Excel installed on your machine then you have access to an IDE that you can use to develop applications. No downloads, no config or version nightmares and no compatibility issues – its there right now waiting for you!
User comfort:
Even the best developers are constrained by one inescapable truth – in many cases the people they develop their software for are of varying skill levels and it is usually necessary to develop for the ‘lowest common denominator’ – the least skilled individual in your target group. Hand-holding through tasks a developer would regard as below trivial is necessary and jargon / command lines are quick ways to alienate your user base.
But these day most businesses where using a computer is part of a person’s day-to-day expect a basic level of competence in a few applications. Excel is one of those, along with Word (or a similar word processor) web browsers and email software.
So, consider a barely-competent computer user who is just about comfortable in Excel.
Building applications into Excel exploit this level of comfort in a way that bespoke apps just do not. Learning how to use a VBA app feels like extending existing knowledge, rather than learning something new. And although the latter sounds like the desirable option for most people who will read this blog, if you are a manager rolling out a time-sheet option to your employees it is adoption and correct use that would be your main targets. Using Excel VBA helps avoid the terror that the barely competent computer user feels when presented with new software that they are required to master.
Summary:
I’ve not gone into any detail on the weaknesses of VBA – there is plenty of disparaging material out there and I am trying to balance that with this article, not add to it! VBA does have weaknesses, as do all development platforms, and these are exacerbated when it is used it for tasks that it is not appropriate for, or when used poorly.
But it has redeeming characteristics, and without having it in my tool-set there are many situations where I simply would not have been able to deliver aspects of whatever job I was doing at the time. I use Java for fun, but for bespoke business apps its a tool I am sure Occam himself would reach for.
If you remain sceptical about the usefulness of VBA read this article detailing a range of real-world solutions I have developed using it.