Comparing Features of Office Scripts and VBA

Office Scripts is the new kid on the block when it comes to automating Excel workbooks. Office Scripts allows you to automate many of the tasks that you can already automate using VBA. Although Office Scripts and VBA perform a similar role, there are some significant differences in the way they go about it.

Which version of Excel do I need?

You can use VBA in any desktop version of Excel for Windows released since 1993 and in any version of Excel for Mac except for 2008.

Does anyone still have a copy of Excel 5.0?

(Image credit: guidebookgallery.org/splashes/excel)

To use Office Scripts, your version of Excel must meet several criteria. Firstly, you need a version of Excel for Windows, Mac or the web with an internal version number of at least 2210.

You can choose File | Account in a desktop version of Excel to see the version number.

Secondly, the licence you use with Excel must be a commercial or educational licence which includes desktop versions of the Office applications. This means that you can't use any of the licences designed for home use.

These subscription plans include the desktop applications but aren't commercial or educational licences.

Even if you have a commercial or educational licence, it might not include desktop versions of the Office applications.

This commercial licence only includes the web versions of the Office applications.

You also need access to OneDrive for Business as this is where all Office Scripts files are stored.

Which programming language do I use?

VBA, unsurprisingly, uses the VBA programming language!

A basic VBA subroutine to assign text to a cell.

Office Scripts uses the TypeScript language, which is based on JavaScript.

A simple Office Scripts function to assign text to a cell.

Although the syntax of the two languages is different, they share many common features.

How do I create the code?

You can use the tools on the Developer tab of the ribbon to start creating VBA code.

You can find tools for VBA on the Developer tab of the Excel ribbon.

To work with Office Scripts you can use options on the Automate tab of the ribbon.

Use the Automate tab of the Excel ribbon to work with Office Scripts.

Where do I edit the code?

In VBA you use the Visual Basic Editor (VBE) to edit your code. This is a separate application (although you launch it from within Excel) which hasn't changed much since its first release.

The VBE still relies on an old-style system of menus and toolbars.

In Office Scripts you edit your code in the Code Editor panel within the Excel window.

The Code Editor is integrated into the Excel application.

If you're writing Office Scripts in the web version of Excel you can also choose to edit your code in Microsoft Visual Studio Code. This is a relatively new application which allows you to edit code in a range of different languages.

Office Scripts uses the web version of Visual Studio Code.

Where is the code stored?

In VBA, you store the code in modules which are held in a VBA project which is saved as part of the workbook.

A module belongs to a project which belongs to a workbook.

In Office Scripts, your code is stored in a script file with an osts extension. Script files are saved to OneDrive or SharePoint and stored separately to the Excel workbooks.

Office Scripts files are saved by default in the Office Scripts folder in OneDrive for Business.

How do I run the code?

Both VBA and Office Scripts allow you to run code directly from their respective editors, or to attach the code to clickable buttons on a worksheet.

VBA can use several types of control including the CommandButton shown here. Office Scripts uses simple shapes like the green rectangle shown here, although you can make lots of formatting changes.

How do others access the code?

VBA code is stored in a workbook so, if a user has access to the workbook, they can run the code within it.

To allow another user to run an Office Scripts script, you have to share the script in a workbook.

Until you choose to share a script, only you will have access to it.

Next: Advantages of Office Scripts

Now that we've compared some of the basic features of Office Scripts and VBA, in the next part we'll see some of the advantages of using Office Scripts.