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;
}
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.
| subject | VBA | Office Scripts |
|---|---|---|
| Execution environment | On individual devices | On the cloud |
| Authority management | password | account |
| 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.
The following graphs were questionnaired in 2021, and the programming languages you learn and use are summarized.
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.
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.
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.
// 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.