Excel is a popular and able piece of software; unfortunately, it is not great at everything. Frequently this leads to people running complex manual procedures when there is not a simple feature to solve the problem.
The built-in solution to this kind of problem is the venerable VBA Macro. I say venerable as VBA has been around for a long time (launching way back in 1993). Over that time, VBA has been used for almost everything and perhaps is best known for creating forms or buttons that run off and do a job.
I recently had a problem, and I needed to create calculations based on simultaneously filtering the same data table in different ways. Think of the problem this way—six different results and six different sets of filters on the same data table.
The solution – create a new custom function. VBA can add new functions usable in a spreadsheet. No forms, no complications. Just a new Excel function that solved the problem.
To the user, it looks like any other function. Start by typing in equals, follow up with the name and press enter for the answer. Very little training is required.
This approach means no particular UI elements, buttons, or keyboard shortcuts—just perfect integration into your spreadsheet.
That’s why I say let’s hear it for the custom function. A simple and elegant solution to spreadsheet customisation
What of the Future?
Earlier I gave away the great age of VBA. Is it still a good idea to write in VBA.
The first answer is this, VBA is here, established and operational. Microsoft will be continuing to support VBA for quite some years to come.
It is not the only choice.
Excel in the Cloud
For Microsoft 365, the browser-based version of Excel runs Office Scripts. If you require to automate Excel in the cloud, then Office Scripts replaces VBA.
- VBA does not run in browser-based Excel
- At the time of writing Office, Scripts does not run in desktop Excel.
That’s a pretty binary choice.
Python and Excel
Python can interact with the Excel object model. Thus, making it possible to work with a spreadsheet using Python.
To use Python each computer must have access to a suitable Python Environment. It is not an out of the box solution.
There are more hoops to jump through from a support point of view; however, if Python is part of your reporting and data analysis regimen, then this is worth it.
Bringing it all together
Extending Excel with scripted functions is a good idea.
On the desktop, VBA remains an out of the box solution.
However, in the cloud, Office Scripts is the way to go.
Desktop VBA will likely get new scripting capabilities in the future, but VBA will be around for a very long time. VBA as a tool is critical for too many businesses to turn it off.
If business intelligence in your organisation uses Python, then consider using Python with Excel. Python adds significant analysis potential.