Skip to main content

Difference from VBA

Office script is a script language that runs on the web version Excel (Excel On the Web).

There is no difference between Office scripts and his VBA, which operates on the Office service developed by Microsoft.

However, there are significant differences in behavior and syntax in each language.

Here, we will explain the difference between Office script in web version Excel and VBA in the on -premises Excel using the following sample code.

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
function cubed(target: number) {
const calculated = target ** 3;

console.log(calculated);
}


function squared(target: number) {
return target ** 2;
}
Contents that you can learn on this page

By reading this chapter, you can understand the following contents.

  • The difference between Office Scripts and VBA

  • Some examples of differences from VBA

  • The formation of the Office Scripts and the share of the base language

  • Basic syntax of Office Scripts

Basic functional differences

Before the difference as a programming language, the tables are summarized in the table.

subjectVBAOffice Scripts
Execution environmentOn individual devicesOn the cloud
Authority managementpasswordaccount
Shareable
Operation record
Other book operation×

The purpose is the same

The behavior and syntax are different, but the purpose of dynamically controlling the data in the office service using the program.

Unlike the function defined on the Excel cell, data can be controlled by the number of seats, the number of rows and columns of the sheet.

In addition, data can be handled beyond the frame of one Excel file, such as control of Excel book itself and cooperation with external services.

About execution environment

The conventional on-premises version of Excel is installed and used on individual PCs, so the execution environment of VBA is also an individual PC.

In the case of Office Scripts, execution environment is on the cloud. It does not consume individual PC resources.

About the base language

As I explained at the beginning, the Office Scripts is based on TypeScript.

TypeScript is a language that adds static modeling function to JavaScript, and except for type definition, the description method is almost the same as JavaScript.

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

Google Apps Script, which can be used as a macro function of Google Spreadsheet, is also based on JavaScript.

Although the functions and method names that manipulate books and seats are different, they are closer to Google Apps Script than VBA.

About JavaScript

JavaScript is the most common language, and is a language that operates on a web browser.

In addition, more and more cases are used as a language that operates on the server using node.js.

note

The following graphs were questionnaired in 2021, and the programming languages you learn and use are summarized.

Share of programming language

You can see that more than 60% of developers use JavaScript.

The JavaScript standard, called Ecmascript, is updated every year, and the Office Scripts seems to be compliant with this, so I think it will be expanded in the future.

There are many situations that can be used, not just Office scripts, so you will not lose them.

Some examples of different points

From now on, I will actually use the Office Scripts to explain the difference from VBA.

Definition of function

In VBA, functions that do not have a return value are defined as Sub, and functions with return values are defined as Function.

The Office Scripts can be defined using function regardless of the return value.

About Arrow operator

TypeScript can reproduce the function using the arrow operator, but is not allowed in the Office Scripts.

Function definitions can only be implemented in function.

Sample of function definition
function squared(target: number) {
const calculated = target ** 2;

return calculated;
}

// The same is true for functions without a return value
function cubed(target: number) {
const calculated = target ** 3;

console.log(calculated);
}

Definition of variable

VBA uses Dim for variable definition and Const for constant definition.

The Office Scripts uses let for variable definitions and const for constant definitions.

VBA has a very strong static format, and it requires a redefinition of variables (Redim) when changing the number of elements later, but it is not necessary in the Office Scripts.

Sample of variable definition
// Definition of variable
let count = 1;

// Add 2 to variables
count += 2;

// There is no need to specify the number of elements at the time of definition
const array: string[] = [];

// Can be added in the array as much as possible later
array.push('Apple');

"If" statement and loop processing

"If" statements and loop processing are explained on the following pages.

"If" statement

Loop processing