Excel Office Scripts: The Future of Automation, Replacing VBA
Excel Office Scripts have ushered in a new era of automation, gradually replacing Visual Basic for Applications (VBA/macros) as the preferred method for automating Excel tasks in both the desktop and online.
VBA’s limitation is it’s tied to the desktop. VBA cannot and never will be able to execute in Excel online.
With Office Scripts, users can record and replay actions, eliminating the need for complex VBA coding and making automation more accessible to a wider range of users.
Another way to write Office Scripts is to get ChatGPT to do it for you!
Join me as we explore the exciting world of Excel Office Scripts and witness the future of automation unfold, saying goodbye to cumbersome code and embracing a user-friendly and efficient way to automate BORING Excel tasks.
Table of Contents
Watch the Excel Office Scripts Video
Download Workbook & Office Script
Enter your email address below to download the files.
Excel Office Script Requirements
Office Scripts is available to Excel users with the following:
- Excel for Windows (version 2210 or higher), Excel for Mac, or Excel on the web.
- OneDrive for Business.
- Any commercial or educational Microsoft 365 license with access to the Microsoft 365 Office desktop apps, such as:
- Office 365 Business or Business Premium
- Office 365 ProPlus or ProPlus for Devices
- Office 365 Enterprise E3 or E5
- Office 365 A3 or A5
- Internet connectivity with connected experiences
Where to Find Office Scripts Tools
The Office Scripts tools are available on the new Automate tab of the ribbon:
On the Automate tab you can record scripts using the ‘Record Actions’ button or write a new script by hand, using the ‘New Script’ button. This opens the Code Editor pane:
In the gallery you’ll find a selection of sample scripts to try, as well as any scripts you’ve recently created.
The ‘All Scripts’ button opens the Code Editor pane where you can navigate to recent scripts, scripts saved in this workbook and samples:
If you don’t see this tab, check your version of Excel meets the requirements above.
Office Script Scenario
A table of contents with hyperlinks to each sheet can make large files easy to navigate for your users.
However, they can be time consuming to create and if you change the name of any sheets, you need to manually edit the hyperlinks.
With Office Scripts we can automate the creation of tables of contents and update them for any changes in the file with one click.
Using ChatGPT to Write Office Scripts
I could have recorded the script, but I would have had to edit it to handle deleting any existing tables of contents in the file before I could update it with a new one.
And with zero JavaScript skills, that’s way beyond my capabilities, but not for ChatGPT.
The key with using ChatGPT to write Office Scripts is to give it a very clear description of what you want.
The ChatGPT prompt I used was:
Write an Excel Office Script to generate a new Table of Contents sheet called "TOC" that contains a hyperlink to each sheet in the current file. If there is already a sheet called "TOC", delete it before creating the new sheet. Start the table of contents hyperlinks in cell B3. Format the hyperlinks in blue font with an underline. Place a heading in cell B1 of the "TOC" sheet with the text "Table of Contents" in black bold font, size 18. Hide the gridlines on the “TOC” sheet. Annotate the script to explain what each step in the script does.
It got a little stuck on the font formatting, but nothing that it couldn’t correct once I fed the errors back into ChatGPT.
The end result is this code:
Inserting the Office Script into Excel
To use the Excel Office Script returned by ChatGPT, follow these steps:
- Open the Excel file you want to add the Table of Contents to.
- On the "Automate" tab
- Click on "New Script" to open the Office Scripts editor.
- Replace the default script with the provided script.
- Click the "Run" button or press "Ctrl + Enter" to execute the script.
Once you’re happy the script runs successfully, click the ‘Save Script’ button to the right of the ‘Run’ button.
Note: to rename the script, click on the name in the Code Editor to bring up the Script name edit box:
Re-using Office Scripts
By default, scripts are saved to your OneDrive account and are available in the script gallery or your script library via the ‘All Scripts’ button on the ribbon.
To attach the script to the current file and share it with anyone who has edit access to the file, you can link to it via a button in the current file.
- Go to the sheet you want the button placed on
- Automate tab > All Scripts
- Click the ellipsis for the script you want to link to in the file > + Add in workbook:
This adds a green button to the current sheet with the name of the script. When clicked, the script will execute:
Go ahead and give Office Scripts a go. Try recording your own and for more complex scripts, reach out to ChatGPT for help.
Using ChatGPT to write Office Scripts often results in methods incompatible with Excel’s Office Scripts, likely due to similarities with TypeScript and Java. This leads to frequent error messages when running scripts. While Copilot may help improve the situation, it’s currently better suited to TypeScript. As of now, Office Scripts can’t trigger on events directly, but Power Automate can be used to manage this. While Copilot and LLM advancements are promising, ordinary users might still face limitations, such as licensing restrictions and inconsistent results from AI systems, which vary in output even with similar prompts. Hopefully, these tools will continue evolving to meet all users’ needs.
Yes, it definitely has room for improvement! Good point about the similarities to Java and TypeScript.
Using Chatgpt to write office scripts,it always gives me some type methods that cannot be used in Excel office scripts. Since it is similar to typescript and Java, I think it sometimes may mix up. Get lots of error messages when running the scripts. Wonder how to solve this situation.
I think in time it will improve, especially with the release of Copilot soon.
Agree! Do you mean the Microsoft copilot? Now Chatgpt is more familiar with TypeScript rather than Office scripts. Hopefully, it will improve in the next generation of LLM and GPT models.
Yes.
I have tried to use Copilot with the same instructions you drafted but it did not work. There were lost of errors.
Hmmm, unfortunately that’s one of the ‘features’ of these AI systems in that they churn out different versions to the same questions.
The licensing requirement seem to indicate that this is ONLY for businesses. Is it likely that ‘ordinary’ users like me will ever get access?
We hope so, Howard, but no idea if there are plans to make it generally available.
Wow! Blown away by how fast you created something so useful. Thank you for sharing the example. I was able to take what you provided in the sample file and make a slight modification to add additional information to the TOC worksheet with just a few iterations using ChatGPT.
Awesome to hear, Brian!
The big difference between VBA and Typescript is that, as far as we know, Typescript can monitor events in the spreadsheet to trigger code execution, whereas VBA can do so. This is a limitation for us. Unless you know otherwise
Hi Paul, I think you meant that Office Scripts ‘can’t’ trigger on events, which is correct. However, you can team it with Power Automate to handle the trigger and execution of the script.
Mynda,
I look forward to your posts and today’s installment exceeded all expectations. I never really thought of scripts outside of my workbooks since I write code for my purposes alone. However, you opened a new door that I need to explore more. Thank you for your continued posts as you add value to my day!
Alan
Wow, that’s awesome to hear, Alan!