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 ^ 2End Functionfunction cubed(target: number) { const calculated = target ** 3;
console.log(calculated);}
function squared(target: number) { return target ** 2;}Differences in basic features
Section titled “Differences in basic features”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.
The goal is the same
Section titled “The goal is the same”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.
About the runtime environment
Section titled “About the runtime environment”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.
About the base language
Section titled “About the base language”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.
About JavaScript
Section titled “About JavaScript”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.
Concrete examples of the differences
Section titled “Concrete examples of the differences”From here on, we’ll use Office Scripts to explain how they differ from VBA.
Defining functions
Section titled “Defining functions”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.
' Use Function for a function that has a return valuePublic Function Squared(ByVal target As Long) Squared = target ^ 2End Function
' Use Sub for a function that has no return valuePublic Sub Cubed(ByVal target As Long) Dim calculated As Long calculated = target ^ 3
Debug.Print (calculated)End Subfunction squared(target: number) { return target ** 2;}
// Functions with no return value are defined the same wayfunction cubed(target: number) { const calculated = target ** 3;
console.log(calculated);}Defining variables
Section titled “Defining variables”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.
' Define a variableDim count As Longcount = 1
' Add 2 to the variablecount = count + 2
' Define an array (to change the number of elements later, declare it without specifying the number of elements)Dim fruits() As StringReDim 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"// Define a variablelet count = 1;
// Add 2 to the variablecount += 2;
// You don't need to specify the number of elements when you define an arrayconst fruits: string[] = [];
// You can add as many elements to the array as you like laterfruits.push('Apple');If statements and loops
Section titled “If statements and loops”If statements and loops are covered on the following pages.