Since 1993, Visual Basic for Applications has been the first choice for Excel automation. As a programming language that was available for business users, millions of macros have been created in VBA. But, is VBA still relevant in 2020?
Popularity in the language has dropped over 60% from it’s peak in 2004. Are business and technical users turning to other solutions to automate Excel?
A quick search on LinkedIn shows that there are still over 15 thousand jobs that are requesting VBA.
How is this possible when VBA is the “most dreaded programming language” according to Stack Overflow in 2020?
VBA is not as dead as you think.
Given that it is so despised, it should be notably less popular than the second most popular language. Excel automation can also use TypeScript to create Office Scripts which can be used to automate Excel Online.
Despite it’s popularity with programmers, TypeScript is not generating as much interest as VBA. In 2020, VBA still shows twice as much interest as a popular programming language that can be used to automate Excel. (The most loved programming language is Rust. VBA shows 7 times the level of interest as Rust.)
VBA is being used primarily by non-IT users.
A major reason why VBA is being used so frequently is that it does not require resources from an IT department.
IT departments have significant other responsibilities other than automation and application development. These responsibilities include administration and tech support, along with customer facing activities such as the corporate web site.
As a result, the time and resources for automating processes can be very limited. Providing training or low-code resources (such as PowerApps) can be timely or costly.
In order to improve efficiency, business users use VBA since there is no administrative control in place to prevent users from writing macros.
For departments that have heavy reliance on VBA, macros may be fully enabled, or disabled with notification. In either scenario, an unsuspecting business user could run malicious code on a .xlsm file.
What can be used in place of VBA in 2020?
There are multiple options that can be used in place of VBA, to create processes that are more secure and scale-able in the future.
- Import and manipulate external data with Power Query (Get and Transform).
- Automate Excel Online with Office Scripts.
- Gather data using a Power App.
- Use Power Automate to automate other O365 applications, such as Outlook.
While some of these solutions require external programs, nearly all of the solutions can be created with a Microsoft 365 Enterprise licence (E3 or E5) or a Microsoft 365 Business Standard licence.
Using Power Query
Until 2010, the easiest way to automatically import data into a spreadsheet was to use VBA code; this could be used to open files, copy data then close files that were opened.
Other methods could be used if the spreadsheet was set up as an ODBC connection, though this requires greater technical expertise.
By using Power Query / Get Data to automate the data import process, code is sustainable, and does not rely as much on the original developer.
Automating Excel Online using Office Scripts
Office Scripts are the newest member of the Excel automation family. They allow for script to be recorded (using a macro recorder) into Excel Online.
All code that is recorded for an Office Script is in TypeScript, which is a much more popular language than VBA.
At this time, Office Script still is a very new product. As a result, there are some features that have not been fully enabled yet.
These include:
- Office Script is only available in Excel Online. Office Script for desktop Excel is still being developed.
- Some functions (the “unknown event” in the image above was a hyperlink removal) can not yet be recorded.
- Office Script is only available for enterprise tenants who have an E3 or E5 level license.
A major difference between Office Script and VBA is that Office Script does not record events; meaning that Office Script cannot determine which cell is currently selected.
An Excel function like the one below would not work in Office Script, as it relies on the End function to move the currently selected cell.
Office Scripts can respond to triggers from Power Automate; however, which provides possibilities to run scripts triggered by PowerApps.
Gather Data using Power Apps
Gathering data with VBA is normally done using a VBA userform. This allows for some level of data validation to be done before the data is submitted to Excel.
This also provides a more customized experience than just using ranges within Excel.
Power Apps with Office 365 can select an Excel Online spreadsheet as a data source. This allows Power Apps to populate and retrieve information directly from a spreadsheet, as long as it is stored in an online location such as OneDrive or SharePoint.
A challenge with PowerApps and Excel is that files must be smaller than 2 MB, and by default only 500 records can be loaded into an app. For larger datasets, other data sources should be used, such as Lists, SQL Server or the Common Data Service.
If no premium licensing is available, business users should have SharePoint lists available for these large data sets.
Use Power Automate to automate other applications
Excel is frequently used as a data source for other applications. An example of this is where a marketing list table is used to send emails to prospective clients.
This process can be automated by adding the Outlook library in VBA. This will allow the code to cycle through rows, picking up relevant information on each row.
It is much easier for a business user to develop a workflow using Power Automate which is included with Microsoft 365 licenses.
Power Automate should not normally be used to automate data manipulation within a spreadsheet.
However; it can be used to create automated processes that interact with cloud products, such as Forms, Planner and Power BI.
The one exception to that is if there is a trigger (such as uploading to a SharePoint library) in order to run an Office Script. This was described earlier in this post.
Conclusion
With tools that are available within Excel, such as Power Query as well as the tools that exist within the Power Platform, the number of use cases for VBA is declining.
Business users will still seek VBA based solutions, but this is primarily due to:
- Lack of permission provided by IT departments.
- Low awareness of alternative solutions to VBA.
- Lack of training on non-VBA based solutions in the Power Platform.