The UCOVI Blog
The UCOVI Blog
Excel VBA in the modern business - the case for and against
Ned Stratton: 13th July 2020
"Did you know that little kittens die every time you write something in VBA Ned?", was a line I was given about 4 years ago at work by a quick-witted Python developer.
But to some, VBA is the magic level above the secret trap door in the haunted house that is Microsoft Excel. So much repetitive labour to be automated, value to be unlocked and fun to be had when Alt + F11 is pressed.
I ran a poll on LinkedIn recently asking if it had a future in the technology stack of a modern business. The No's took it 53% - 47%, but for a language that's constantly written off as on its way out, 47% is not an insignificant minority.
So what is it about Excel VBA that incurs the contempt of developers and data engineers, who see it as PlayDoh to their Scalextric? And what is it about it that makes it paradoxically still so widely used and stubbornly hard for Microsoft to kill off?
Why VBA is still popular
As the poll suggests, it still enjoys a surprisingly healthy fanbase among data analysts, budding programmers and other technical people in SMEs for the following reasons:
Ease to set up
Working in Excel VBA takes the IT faff out of programming – it really is Alt +F11 and go. No downloading and installing of IDEs and dependent libraries for a whole morning before you write your first line of code. And crucially, no dreaded 'You need administrator privileges to install this on your PC' message, which is the bane of every data professional's life.
Versatility, as long as your business uses Excel...
...which is still the case in most SMEs and the public sector, so no need for the caveat. Spreadsheets just come naturally to people at work as a way of analysing data, organising their teams and projects, or digesting reports. Yes, there are now newer, more sophisticated alternatives, like Power BI for data analysis/visualisation or Monday.com for team and project management. But these are the almond milk Mocaccino and Masala Chai to Excel's office-kitchen instant coffee with milk and sugar. Like it or not, most people are still drinking the latter because it's right there in front of them for free.
Since Excel is so ubiquitous, a programming language dedicated to optimising its use then becomes pretty handy to know, and if there's a task Excel can be used for, there's a way VBA can automate it. This is because VBA is an object-orientated programming language like Python, and every chart, formula or other functionality (such as Text To Columns or Goal Seek) can be called and customised with variables within VBA script. This makes it possible to write a single script to run 15 minutes of copy, paste, =LEFT(VLOOKUP(A2,Sheet1!A2:B147,2,FALSE),5) + "Sales", insert pivot table, show in tabular form, and save as new workbook, that a human would otherwise do incorrectly.
Depth and breadth of support
As I'll cover later, VBAs most likely competition for its role in data cleaning and automating visuals will be Power BI, which has been making waves since it started in 2015. But 5 years of existence means 5 years' worth of documentation and online support, as opposed to Excel VBA's 25 years and more.
This manifests itself in the comparative experience that I and other data folk get when Googling for a solution to basic Power BI problems like 'How to show empty values on a matrix as zero' (impossible to find), against a sophisticated VBA question such as 'Excel VBA how to generate unique, randomly sorted array of n numbers between x and y' (reproducible Stack Overflow solution at first attempt).
Reasons for unpopularity
VBA's main detractors are the more serious, established programmers and software engineers, and senior technical leaders and managers. Here's why:
Slow and restrictive
You may not have to download, install or bribe your IT guy for his admin password to get it, but the Visual Basic Editor really is the poorest of poor men's IDEs. It has next to no intelli-sense or helpful colour-coding, a mind of its own when it comes to providing pick-lists of available objects and methods, and gives you annoying pop up boxes mid-typing at every syntax error.
Syntactically, the VBA language is verbose and achieves in 20 lines what Python or JavaScript could do in 10, and the language runs slowly on datasets bigger than the 100,000 row mark, and is by implication limited to 1.1 million rows (the limit of an Excel spreadsheet).
Furthermore, Excel VBA isn't truly automatable; someone still has to open the spreadsheet and click the button assigned to the macro. (Though theoretically you could build a Python script to automate the opening of the Excel file and the clicking of said button, which you could trigger from a Windows Task Scheduler job. But if you've made the effort to install and script the Python you may as well write the VBA macro in Python as well.)
Principal enabler of 'Business by spreadsheet'
The paradigm of data and IT infrastructure is integration. The depressing reality for businesses that fail to meet this (of which there are many) is product orders data on the CRM, sales leads on SalesForce, and everything else on Diane from Finance's master spreadsheet. To blend these together for even basic insights requires...more spreadsheets.
The impulse that might drive a company to put a stop to ingrained Excel-ery is not the inherent risk, vulnerability and general shoddiness of it, but rather the man hours it wastes. So when genius Mr VBA developer enters the fray and automates the spreadsheets, enough cost and time is seen to be saved to justify maintaining business process by Excel and not going ahead with systems upgrades. Not great 6 months later when Mr VBA developer discovers he likes DAX Tabular modelling instead, gets another job and the macros stop working…
Unwanted democratisation of programming
Software engineers, IT and data professionals are a proud bunch. This is because they see themselves as having mastered dry, deeply analytical and number-crunchy subject matter that mere mortals don't have the patience for. Excel VBA is the Q-jump entry into this elite club for those without Computer Science degrees because it lets you learn the principles of coding by building scripts on your own spreadsheets, ably guided by Stack Overflow posts. Once you get going on VBA and cleaning big spreadsheets, Python, JavaScript and SQL are suddenly within your grasp. The more people that take the Excel VBA route into programming, the more the market is saturated and diluted and the less elite the club becomes.
Damn Excel VBA!
The VBA Verdict: Here to stay but fading fast
In ten years' time from now, Excel VBA and the Visual Basic Editor will still be available and - rather like some time capsule from the year 2000 - preserved in its current state.
Its user base will continue to shrink, but Microsoft won't pull the plug because blanket removal of functionality from a product is such a software faux-pas, lest someone somewhere is still using it. This is the reason that Excel 4.0 Macro sheets – 4.0 being the 1992 version - are still available today.
Since VBA will still be available, existing developers will continue to make use of it to solve business challenges.
But the VBA developer community only keeps going when business-user Excel wizards looking for that first step into coding pick it up and experiment with it on their lunchbreaks. This will stop. First, online coding courses for more widely applicable languages are growing relentlessly in quantity, quality and cost-availability. Second, Power Query and Power BI will take away the two biggest reasons to learn or use it.
I ran a second poll in a VBA specialists' group on LinkedIn asking participants to vote on their most common uses for it. The top two were automating visuals on dashboards (33%), which is what Power BI is now best in class at, and cleaning data/ETL (28%). For the latter, Power Query will take over completely. Imagine the Record Macro button but with a whole user interface and full auditability of each transformation step. In terms of attracting fledgling coders who would otherwise learn VBA, Power Query has this covered too with the M language it provides under the hood, which users are starting to see the value in learning and trainers are now offering paid courses for.
After all, people would much rather learn a language named after a James Bond character.
⌚ Back to Latest Post