557 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Comparing Office Scripts with VBA
Part two of a four-part series of blogs
Until recently, if you wanted to automate a Microsoft Excel workbook your only real choice was the VBA (Visual Basic for Applications) programming language. Now, there's a new option for adding code to Excel: Office Scripts for Excel. There are many similarities between Office Scripts and VBA, as well as some significant differences. This blog provides a comparison of the two systems.
So far we've seen that Office Scripts and VBA are pretty similar. This part of the blog shows some of the things that Office Scripts can do better than VBA, or that VBA can't do at all!
A significant limitation of VBA is that it only runs in the desktop version of Excel. You can run code written in Office Scripts in both the desktop and web versions of Excel.
You can't do this with VBA.
Controlling security in VBA is complicated, to put it mildly. The problem arises from the fact that VBA has the same level of access to your computer as Excel itself. This means that malicious code can cause a lot of damage to an unsuspecting user's computer.
To help prevent malicious VBA code from running automatically, a user can choose from several security levels in Excel which controls what happens when they open a file containing VBA code.
These options only control what happens when you open a file which contains VBA code.
Microsoft recently added a feature which automatically disables VBA in a file downloaded from the Internet and which works independently of the options shown above.
This is the message you'll see when opening files which contain VBA from the Internet.
To allow the code to run you must change a setting in the properties of the file before you open it.
You can unblock the code the File Properties dialog box.
Of course, if a user chooses to enable the VBA code there's no guarantee that it won't do something malicious!
An administrator can disable VBA for everyone in an organisation but that means that nobody can run any non-malicious, useful code either.
Office Scripts solves these problems by severely restricting what the code has access to. In an Office Scripts script your code can only talk to the Excel file that the code is running in, simple! An administrator can also control which users have access to Office Scripts, providing more control than in VBA.
You can enable Office Scripts for only the most responsible people in your organisation.
You can execute an Office Scripts script as part of a Power Automate flow. This is a great way to execute a script on a schedule.
This flow executes Script 1 on a file called Book.xlsx on a daily basis.
The error handling system in VBA is hopelessly archaic. It involves using GoTo statements to jump to different sections of code to resolve a runtime error, followed by Resume statements to get back to the original bit of code.
Good luck keeping track of where your code will be in a procedure like this!
Office Scripts uses a modern, inline style of error handing involving try catch blocks.
Error handling in Office Scripts has a more natural reading flow compared to VBA.
The code editor for Office Scripts, while quite basic, has a much more modern feel compared to the Visual Basic Editor. The IntelliSense feature is more responsive than the equivalent feature in VBA.
The IntelliSense list prompts you with available options as soon as you begin typing, unlike in VBA where you often need to press a keyboard shortcut to make it appear.
The tooltips which offer help on keywords are more descriptive than their VBA equivalents.
The tooltip describes each parameter in detail.
In VBA, tooltips are somewhat simpler.
To find out more information about a keyword you have to look it up elsewhere.
VBA's last major update was to version 7.1 in Office 2013. It also continues to receive minor updates in line with new features that are introduced to Excel. In the long term, however, the potential for major changes appears to be small.
Office Scripts is still at a relatively early stage of development - at the time of writing, it doesn't even have its own Wikipedia entry! The potential for big changes is huge, as evidenced by some of the recently released features such as the ability to record scripts in Excel for desktop, and the features currently in preview, such as integration with Visual Studio Code.
One of the upcoming features of Office Scripts.
Of course, there's also the potential for Microsoft to get bored with the project and abandon it completely...
Now that we've seen some situations where Office Scripts has the edge, the next part of the blog explains why VBA isn't quite obsolete just yet.
|Parts of this blog|
25 Aytoun Street