Advantages of Office Scripts

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!

Automating workbooks in Excel for the Web

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.

Better security

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!

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.

Power Automate integration

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.

Better error handling code

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.

A modern code editor

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.

Potential for improvements

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.

Next: Advantages of VBA

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.