Basic usage of variables.(Microsoft Excel Office Scripts)


Japanese version.

Basic usage of variables in Office scripts.


A variable is like a box in which data is temporarily stored.

Programming languages such as Office Scripting and VBA (Macro) make extensive use of these variables.

Variable declarations and data type

To use a variable, it must be declared.

To do so, use the following syntax (Red text should be changed accordingly)

let variable name: data type;

Variable names are optional. Use the name of the variable as the name of what kind of data you want to put in it.

(However, the same variable name cannot be declared more than once.)

Data types are fixed and frequently used ones are as follows

Data typeData handledMacro (VBA)
booleantrue or falseBoolean
any AllVariant

Although any type can contain any data,
However, the data type must be converted when it is used.
In addition, the behavior may not be stable,
It is best to avoid the use of any as much as possible and use other data types.

Two Date types are provided to create both a date and a date/time.

The any type cannot be created by just declaring it.

Initialized at the same time as declaration

Using =, the value can be set at the same time as the declaration (initialization of the variable).

let variable name: data type = value;
Data typeValue description rulesExample
stringSurrounded by "."ABC"
numberMust be recognizable as a numerical value.123.123
booleantrue or falsetrue
Datenew Date(year,month - 1 ,day)
new Date(year,month - 1 ,day,hour,minute,second)
new Date(2021,6 - 1,1)
new Date(2021,6 - 1,1,12,30,45)
anyNo data type is specified,
and as one of the other data types

The Date type must be new.

Also, the month range is 0 to 11 (0 being January and 11 being December). Note that the -1 is required.

Set (assign) a value to a variable

To set a variable to a value, do the following (also called variable assignment)

variable name = value;

It is the same as initialization at declaration except there is no let and data type.

It is also possible to specify another variable or a mathematical expression such as addition, subtraction, multiplication, or division for the set value.

Set the value from a variable to a cell


The following syntax is used:

workbook.getWorksheet("Sheet name").getRange("Cell address").setValue(variable);

Below are examples for each data type.

"MIN_SAFE_INTEGER" and "Number.MAX_SAFE_INTEGER" are the maximum and minimum values of type number. When executed, the result is as follows.


Conversion is required because the format is not recognized by Excel as a date and time.


For those who want to learn Office script effectively

The information on this site is now available in an easy-to-read e-book format.

Or Kindle Unlimited (unlimited reading).

You willl discover how to about basic operations.

By the end of this book, you will be equipped with the knowledge you need to use Excel Office Script to streamline your workflow.


Office Scripts Articles