This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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. Comparing Features of Office Scripts and VBA Advantages of Office Scripts Advantages of VBA (this blog) Conclusions

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.

Comparing Features of Office Scripts and VBA
Advantages of Office Scripts
Advantages of VBA (this blog)
Conclusions

Posted by Andrew Gould on 29 June 2023

In this blog

Coverage of Excel features
User interface elements
Event procedures
External libraries
Debugging features
Online materials
Next: A Summary

Advantages of VBA

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!

Coverage of Excel features

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.

Unrecordable commands in Office Scripts.

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.

User interface elements

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..

Input boxes;

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.

Event procedures

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.

External libraries

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.

External Reference Libraries in a VBA Project.

A small selection of the many external libraries you can reference in a VBA project.

Debugging features

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.

A console.log statement in Office Scripts.

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!

Online materials

Due to its longevity, VBA has a wealth of resources available to help you learn how to use it. This includes, of course, Wise Owl's own VBA courses, videos and tutorial series!

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:

Resource	Description
Office Scripts Documentation	Microsoft's official documentation for Office Scripts. You'll find tutorials, code samples and a reference section for specific commands.
JavaScript Documentation	Mozilla's official JavaScript documentation. Office Scripts uses the TypeScript language which is based on JavaScript. You can learn lots of general information about the language here.
TypeScript Documentation	The official documentation of TypeScript. This site is useful for seeing some of the extra things you can do beyond standard JavaScript.

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!

Next: A Summary

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
Comparing Features of Office Scripts and VBA Advantages of Office Scripts Advantages of VBA (this blog) Conclusions

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR