Basic usage of variables.(Microsoft Excel Office Scripts)
Basic usage of variables in Office scripts.
Operations
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 type | Data handled | Macro (VBA) |
|---|---|---|
| string | Test | String |
| number | numerical | Integer,Singleなど |
| boolean | true or false | Boolean |
| any | All | Variant |
| Date | Datetime | Date |
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 type | Value description rules | Example |
|---|---|---|
| string | Surrounded by ". | "ABC" |
| number | Must be recognizable as a numerical value. | 123.123 |
| boolean | true or false | true |
| Date | new 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) |
| any | No data type is specified, and as one of the other data types | "ABC" |
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
string、number、boolean
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.
Date
Conversion is required because the format is not recognized by Excel as a date and time.
variable.toLocaleString('en-us')
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.