There’s a new way to program or automate actions in Microsoft Office called Office Scripts. We’ll explain what it is, how it’s different from Javascript or VBA and who can get it.
Office Scripts is available for some commercial users in Excel but it’ll spread to other Office apps and possibly/hopefully non-Business users. Even if you’re not interested now, they’ll likely become relevant in the future.
According to Microsoft, Office Scripts:
“allow you to record and replay your Excel actions …”
Which doesn’t seem like a big deal because VBA can already do that. The change is in the following phrase:
… on different workbooks and worksheets”
Scripts are stored in the Microsoft cloud store for your organization unlike VBA which is saved with a workbook or file linked to a workbook.
Action Recorder
Like VBA, Scripts can be made by recording steps done manually.
The interface is a lot clearer than the VBA ‘Record Macros’.
Record Actions makes an Office Script that you can use or, more likely, adapt.
Script code
Office Script code might come as a shock to anyone accustomed to VBA. Once you look past the style differences, a lot of the concepts in VBA continue in Office Script, such as objects, ranges and collections.
Office Scripts use the Typescript language, a Javascript-like system, hence the semi-colons at the end of each command.
Sharing Scripts
Office Scripts are stored in the cloud and then linked to a workbook to allow anyone with edit access to the workbook permission to run the script.
Available scripts appear in the Automate tab
Office Scripts with Power Automate
Because Office Scripts can be saved in the cloud, they can be used independently of workbooks. Users could open a workbook then choose from a library of centralised scripts.
A good example of this is Power Automate, Microsoft’s IFTTT system for commercial users.
For example, Power Automate can be triggered when a new file is added to an online folder. A newly arrived file can be manipulated automatically by a Script. That’s something VBA can’t do.
What about VBA?
VBA isn’t going anywhere. Existing and new VBA code will continue to work for the foreseeable future.
Who gets Office Scripts
Right now, Office Scripts have very limited access, you need:
- Only Excel on the web has Script access for now.
- The workbook saved to OneDrive for Business
- A Microsoft 365 commercial or education licence which includes access to Office 365 desktop apps. That means plans like Business Basic do NOT qualify but Business Standard does.
Microsoft’s plan is to extend Office Scripts to at least Word and PowerPoint plus, of course, the desktop apps.
Hopefully, Office Scripts will be expanded to allow all Microsoft Office 365 users access. If Microsoft wants Scripts to succeed, it needs to be available to all.
More info
Microsoft has published a lot of information about Office Scripts.
- Office Scripting fundamentals
- Record, edit, and create Office Scripts in Excel on the web
- Read workbook data with Office Scripts in Excel on the web
- Sample Excel Scripts
- ExcelScript reference
- Using built-in JavaScript objects in Office Scripts
- Best practices in Office Scripts
- Office Scripts Dev Center
Curious about how Scripts are shared?
Microsoft 365 Admins can control access to Office Scripts.
Being Microsoft documentation, there’s no place for bugs or limitations. There’s sure to be problems with this first public release of Office Scripts plus things that are possible in VBA but not (yet) in Scripts. That’s left for paying customer to waste time discover on their own.
