560 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 one 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.
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.
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.
VBA, unsurprisingly, uses the VBA programming language!
A basic VBA subroutine to assign text to a cell.
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.
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.
Both VBA and Office Scripts allow you to write your code from scratch or to record your actions and edit the code that is generated for you.
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.
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.
You can also export a VBA module to a separate file with a bas extension. You would then need to import the module into a workbook in order to make use of the code that it contains.
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.
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.
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.
Adding a button to run a script automatically shares the script in the workbook.
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.
|Parts of this blog|
25 Aytoun Street