Skip to content

Differences from VBA

Office Scripts are a scripting feature that runs in Excel on the web, Excel for Windows, and Excel for Mac.

Office Scripts and VBA are both automation tools developed by Microsoft that run in Office products.

However, the two languages behave very differently and have very different syntax.

This article uses sample code like the following to explain the differences between Office Scripts and VBA in the desktop version of Excel.

Switch tabs to compare nearly equivalent code in each language.

Public Sub Cubed(ByVal target As Long)
Dim calculated As Long
calculated = target ^ 3
Debug.Print (calculated)
End Sub
Public Function Squared(ByVal target As Long)
Squared = target ^ 2
End Function

Before we look at how they differ as programming languages, the following table summarizes how they differ as features.

Item VBA Office Scripts
Supported platforms Desktop (Windows, Mac) Web, Windows, Mac
Scope of access Entire PC (same permissions as Excel) The target workbook only
Permission management Password Account
Sharing Yes Yes
Recording actions Yes Yes
Working with other workbooks Yes No
Running from Power Automate No Yes
License Included with desktop Excel An eligible Microsoft 365 license

Note: “Running from Power Automate” refers to cloud flows. In Power Automate for desktop, you can run VBA macros with the “Run Excel macro” action.

Their behavior and syntax differ, but the goal is the same: to control data in Office services dynamically with a program.

Unlike functions you enter in Excel cells, they let you control data without being limited by the number of sheets or the number of rows and columns in a sheet.

They also let you do things that are hard to do with cell functions, such as connecting to external services (external calls with fetch) and working with other workbooks and services through Power Automate.

VBA is designed for desktop Excel and runs on each individual PC. Because it has the same permissions as Excel, it can also access files and other applications on the PC.

Office Scripts, on the other hand, run on a JavaScript runtime that’s shared across platforms, and they’re designed to behave the same way no matter which environment you run them from. They can access only the target workbook, not the PC that’s running the script.

You can also run Office Scripts from Power Automate flows, which lets you update a workbook without opening Excel, triggered by events such as a schedule or an incoming email.

Office Scripts are based on TypeScript.

TypeScript is a language that adds static typing to JavaScript, and apart from type definitions, you write it almost exactly the same way as JavaScript.

As a language, closer to Google Apps Script than to VBA

Section titled “As a language, closer to Google Apps Script than to VBA”

Google Apps Script, which you can use as the macro feature of Google Sheets, is also based on JavaScript.

The functions and method names for working with workbooks and sheets are different, but Office Scripts are closer to Google Apps Script than to VBA.

JavaScript is considered one of the most widely used languages, and it runs in web browsers.

It’s also increasingly used on servers through Node.js.

The JavaScript standard, called ECMAScript, is updated every year, but keep in mind that the TypeScript available in Office Scripts is fixed at version 4.0.3, so you can’t use TypeScript features added after that version.

That said, JavaScript and TypeScript are useful in many situations beyond Office Scripts, so they’re well worth learning.

From here on, we’ll use Office Scripts to explain how they differ from VBA.

In VBA, you define a function that has no return value as a Sub, and a function that has a return value as a Function.

In Office Scripts, you can define a function with function whether or not it has a return value.

Function definition sample
' Use Function for a function that has a return value
Public Function Squared(ByVal target As Long)
Squared = target ^ 2
End Function
' Use Sub for a function that has no return value
Public Sub Cubed(ByVal target As Long)
Dim calculated As Long
calculated = target ^ 3
Debug.Print (calculated)
End Sub

In VBA, you use Dim to define variables and Const to define constants.

In Office Scripts, you use let to define variables and const to define constants.

In VBA, if you want to change the number of elements in an array later, you have to declare it as a dynamic array and then redefine it with ReDim. In Office Scripts, this isn’t necessary.

Variable definition sample
' Define a variable
Dim count As Long
count = 1
' Add 2 to the variable
count = count + 2
' Define an array (to change the number of elements later, declare it without specifying the number of elements)
Dim fruits() As String
ReDim fruits(0)
' When the number of elements changes, you need to use ReDim (Preserve keeps the existing values)
ReDim Preserve fruits(1)
fruits(1) = "Apple"

If statements and loops are covered on the following pages.