557 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 three 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.
While Office Scripts has some advantages over VBA, there are several good reasons why it can't be considered a complete replacement for the older language just yet. This part of the blog shows why there's plenty of life left in VBA!
VBA has commands for a wider range of Excel features than Office Scripts. For instance, Office Scripts can't work with separate chart sheets, nor can it perform seemingly simple tasks like saving a workbook.
When recording a script, particularly in the desktop version of Excel, you'll often find that Office Scripts isn't capable of performing an action.
VBA can use a wide range of user interface features to communicate with the user. These include message boxes;
You can use a VBA message box for presenting information and asking simple questions..
You can use an input box for questions that require a longer answer.
And user forms.
You can create a VBA user form for almost anything you like! You can also embed these form controls as objects on a worksheet.
You can display these user interface elements at any point in a VBA procedure, wait for the user's response and then decide what to do based on the user's answer.
In Office Scripts you can use the console.log method to write a simple message to the Output window in the Code Editor.
Not quite as fancy as the options available to VBA.
For anything more complicated than this you'll need to use a worksheet to communicate with the user. The user will need to enter any inputs before the script runs - there's no way for a script to ask for user input while it's running.
You can attach VBA code to a wide range of events associated with the workbook or individual worksheets and charts. The VBA code will run automatically when the event to which it is attached is triggered.
A small selection of the many workbook events to which you can attach VBA code.
There are no event procedures in Office Scripts.
We've already mentioned that Office Scripts can't access anything beyond the single workbook in which the code is running. VBA can not only open and communicate with other Excel workbooks, it can also talk to other applications entirely! A VBA procedure stored in an Excel workbook could open a web browser to scrape data from a website, load the results into a SQL Server database, execute a query on the database to return the data to Excel, create a PowerPoint presentation or Word document to present the results and then use Outlook to email the final report to your end user.
A small selection of the many external libraries you can reference in a VBA project.
Apart from the ability to write messages to the Output window, Office Scripts doesn't have any useful debugging features to help when you're trying to work out what's going wrong.
This is about as sophisticated as debugging gets in Office Scripts.
In VBA you can write messages to the Immediate window, step through your code line by line, set breakpoints to pause your code at specific points, and set watches on variables to see when they change.
The Visual Basic Editor has an entire menu devoted to debugging!
The first of hundreds of Wise Owl videos on Excel VBA.
Office Scripts has far less material devoted to it, due simply to its relatively recent introduction. You can see a few helpful resources for Office Scripts in the table below:
Microsoft's official documentation for Office Scripts. You'll find tutorials, code samples and a reference section for specific commands.
We've recently started running an Introduction to Office Scripts training course. Look out for more Wise Owl training materials for Office Scripts coming soon!
Now that we've seen some detailed differences between VBA and Office Scripts, what conclusions can we draw? Read the next part of this blog to find out.
|Parts of this blog|
25 Aytoun Street