Conditional branches and logical operators.(Microsoft Excel Office Scripts)

04/14/2023

Japanese version.

This section introduces the basic use of conditional branches and logical operators in Office scripts.

Operations

There are three methods of conditional branching.

Conditional branchFeature
if / elseDifferent codes are executed depending on the result of the conditional expression.
switchExecute different codes depending on the value of the variable.
Ternary (conditional) operatorReturns a value depending on the result of the conditional expression.

At a minimum, it is sufficient to be able to use the "if / else" operators, but it is also useful to know the "switch" and "ternary (conditional)" operators,

However, there are also situations where it is useful to remember "switch" and "ternary (conditional) operators".

Conditional Expressions and Logical Operators

A conditional expression is an expression whose result is of type boolean (true or false).

A simple example is shown below. (The "num === 11" in setValue is a conditional expression.)

The result is true if the variable num is 11.

function main(workbook: ExcelScript.Workbook) {
  let n1: number = 11;

  workbook.getWorksheet("Test").getRange("A2").setValue(n1 === 11);
}
OperatorContentsExamples showing the useConditions that must be truePriority
(The smaller the number, the higher the priority)
<less thanValue A < Value BValue A is less than value B1
<=less than or equalValue A <= Value BValue A is less than or equal to value B1
>greater thanValue A > Value BValue A is greater than value B1
>=greater than or equalValue A >= Value BValue A is greater than or equal to value B1
==equivalenceValue A == Value BValue A equals value B2
!=inequalityValue A != Value BValue A and value B are different (opposite of equivalent)2
===exact equivalenceValue A === Value BValue A equals value B2
!==exact inequalityValue A !== Value BValue A and value B are different (opposite of equivalent)2
&&AND (logical conjunction)Condition A && Condition BCondition A and B are both true3
||OR (logical OR)Condition A || Condition BEither Condition A or Condition B is true4

Priority can be adjusted by enclosing it in parentheses ().

AND (logical conjunction) and OR (logical disjunction)

AND and OR compare conditions (true or false). (or a variable of type boolean).

Other comparison operators are comparisons of values of type number, etc.

For example, the following expression would be true only if both variables A and B are less than 10.

Value A < 10 && Value B < 10

Given the following equation,true if either variable A or B is less than 10.

Value A < 10 || Value B < 10
function main(workbook: ExcelScript.Workbook) {
  let a: number = 9;
  let b: number = 11;

  workbook.getWorksheet("Test").getRange("A2").setValue(a < 10 && b < 10);
  workbook.getWorksheet("Test").getRange("A3").setValue(a < 10 || b < 10);
}

Equivalent and strictly equivalent

In strict equivalence (inequivalence), a more rigorous comparison is performed, being aware of data types, etc.

Strict comparisons are safer, so use ==== and ! === should be used mainly.

Conditional branching by if / else

The most basic conditional branch is the if/else. There are various usage patterns for this.

Only if certain conditions are met (if)

If only "if" is written, the branch will branch only when a specific condition is met.

  if (conditional expression) {
    Processing when the conditional expression is true
  }
function main(workbook: ExcelScript.Workbook) {
  let num: number = 10;
  if(num <= 10){
    workbook.getWorksheet("Test").getRange("A2").setValue("10 or less.");
  }
}

If certain conditions are met (if) and if not (else)

if (conditional expression) {
    Processing if the conditional expression is true
}else{
    Processing if the conditional expression is false
}
function main(workbook: ExcelScript.Workbook) {
  let num: number = 11;
  if(num <= 10){
    workbook.getWorksheet("Test").getRange("A2").setValue("10 or less.");
  }else{
    workbook.getWorksheet("Test").getRange("A2").setValue("Greater than 10");
  }
}

If there is no processing for if and only processing that corresponds to else

First, review the conditional expression and consider using only "if" (e.g., change "if" to "a>10" for a <= 10 "else").

(In the case of "else" with a <= 10, change "if" to "a>10", etc.)

In the case of a variable of type "boolean", it is better to use the variable a === false or ! variable a, you can use only if.

This if can be written more than once. (In that case, only the last one can be an "else if" and only the last one can be an "else".)

  if (conditional expression A) {
    Processing when conditional expression A is true.
  }else if (conditional expression B) {
    if conditional expression A is false and conditional expression B is true.
  }else if (conditional expression C) {
    Processing when both conditional expressions A and B are false and conditional expression C is true 
  }else {
    Processing when conditionals A, B, and C are all false 
  }

Nested conditions

It is possible to put if under if.

  if (conditional expression A) {
    if (conditional expression B) {
      Processing when both conditionals A and B are true
    }

    if (conditional expression C) {
      Processing when both conditional expressions A and C are true (In this case, conditional expression B is irrelevant unless the processing is terminated or something in the if of conditional expression B.)
    }
  }

However, if the hierarchy becomes too deep, it becomes difficult to read.

Since AND (logical product) and OR (logical OR) are more efficient than nesting in some cases

If there are more than three levels, it is better to use AND (logical product) and OR (logical OR) or

function to avoid nesting.

The preceding if and the following code work the same way. Which one is better is a case-by-case basis.

  if (conditional expression A && conditional expression B) {
      Processing when both conditional expressions A and B are true.
　}

  if (conditional expression A && conditional expression C) {
      Processing when both conditional expressions A and C are true (in this case, conditional expression B is irrelevant unless the process is terminated in the previous if, etc.)
　}

Conditional branching by switch

If there are multiple value matches, switch is appropriate.

However, if there is an if, switch is not always necessary,

A switch is more appropriate than if in cases where there are multiple simple comparisons, such as when the process changes depending on which button is pressed,

switch is more appropriate than if in cases where there are multiple simple comparisons, such as when the process changes depending on which button is pressed.

switch (variable) {
  case value 1: 
    process when value 1 is matched
    break;
  case value 2:
    process when value 2 is matched
    break;
  case value 3:
    process when value 3 is matched
    break;
  default: 
    case none of the values match
}
function main(workbook: ExcelScript.Workbook) {
  let pushedButton:string = "OK";
  switch (pushedButton) {
    case "OK":
      workbook.getWorksheet("Test").getRange("A2").setValue("A");
      break;
    case "Cancel":
      workbook.getWorksheet("Test").getRange("A2").setValue("B");
      break;
    case "NG":
      workbook.getWorksheet("Test").getRange("A2").setValue("C");
      break;
    default:
      workbook.getWorksheet("Test").getRange("A2").setValue("D");
  }
}

The if statement would look like this.

In this case, it is the switch one that is easier to read and grasp later.

function main(workbook: ExcelScript.Workbook) {
  let pushedButton:string = "OK";

  if (pushedButton === "OK") {
    workbook.getWorksheet("Test").getRange("A2").setValue("A");
  }
  if (pushedButton === "Cancel") {
    workbook.getWorksheet("Test").getRange("A2").setValue("B");
  }
  if (pushedButton === "NG") {
    workbook.getWorksheet("Test").getRange("A2").setValue("C");
  }
  else{
    workbook.getWorksheet("Test").getRange("A2").setValue("D");
  }
}

If there is no break

If there is no break, execution continues without stopping at case.

In the following example, everything from case to default of Cancel is executed.

Although the frequency is very small, there are cases in which the break is removed to allow OR (logical OR) like operation.

There are cases in which break is removed on purpose. (In that case, if is safer.)

function main(workbook: ExcelScript.Workbook) {
  let pushedButton:string = "OK";
  switch (pushedButton) {
    case "OK":
      workbook.getWorksheet("Test").getRange("A2").setValue("A");
    case "Cancel":
      workbook.getWorksheet("Test").getRange("A3").setValue("B");
    case "NG":
      workbook.getWorksheet("Test").getRange("A4").setValue("C");
    default:
      workbook.getWorksheet("Test").getRange("A5").setValue("D");
  }
}

Conditional Branching with Ternary (Conditional) Operators

While if executes the processing depending on the result of the conditional expression in the code, ternary (conditional) operator only returns a value.
The following statement is used.

Conditional expression ? Value if conditional expression is true : Value if conditional expression is false

If you use it in the wrong place, it will be difficult to understand You can forget about it (unless someone else used it), but
It is effective in the following cases (especially if the if nesting is advanced)

Value Assignment

This may be useful in cases where the value to be assigned changes depending on the value of the variable.

function main(workbook: ExcelScript.Workbook) {
  let n: number = 10;
  let s: string = "";

  s = n===10 ? "OK" : "NG"

  workbook.getWorksheet("Test").getRange("A2").setValue(s);
}

Divide by zero with the possibility of dividing by zero

If the denominator in a division is a variable, the variable may be zero.

(Sometimes called "zero division.")

Dividing by zero produces a special result called Infinity,

subsequent processing may be inconvenient. (If Infinity is set to a cell, it is blank.)

This is useful for dealing with such cases.

function main(workbook: ExcelScript.Workbook) {
  let sales: number = 0;
  let cost: number = 1000;
  let costRate: number;

  costRate = sales === 0 ? -1 : cost / sales

  workbook.getWorksheet("Test").getRange("A2").setValue(costRate);
}

Variable Declaration and Scope

Variables declared with let are available only in the area (block) enclosed by {} and It can only be used in blocks lower than {}.

Variables declared in if and switch blocks are not accessible outside the loop.

In the following example, ifOutValue is still available in line 10, ifInValue is not available on line 11.

function main(workbook: ExcelScript.Workbook) {
  let ifOutValue: number = 20;

  if(true){
    let ifInvalue: number = 100;
    workbook.getWorksheet("Test").getRange("A1").setValue(ifOutValue);
    workbook.getWorksheet("Test").getRange("A2").setValue(ifInvalue);
  }

  workbook.getWorksheet("Test").getRange("B1").setValue(ifOutValue);
  workbook.getWorksheet("Test").getRange("B2").setValue(ifInvalue);
}

Variables used only in blocks should be declared in the block.

This makes it easier for others to understand the structure when they read it.

(Because they will know that the variable is not used outside of the block.)

After leaving the block, you can declare a variable with the same name as the variable in the block,

However, this is deprecated because it is very complicated.

Variable declarations with var (deprecated)

Variables can be declared with the statement var instead of let.

With var, though, variables declared inside a block can be accessed outside the block,

This characteristic is useful at the programming stage, but detrimental to later reading.

If you are familiar with JavaScript, you will tend to use var, so I will describe it here.

(because let was not available in some older browsers).

However, it is better to use "let" instead of "var".

function main(workbook: ExcelScript.Workbook) {
  var ifOutValue: number = 20;

  if(true){
    var ifInvalue: number = 100;
    workbook.getWorksheet("Test").getRange("A1").setValue(ifOutValue);
    workbook.getWorksheet("Test").getRange("A2").setValue(ifInvalue);
  }

  workbook.getWorksheet("Test").getRange("B1").setValue(ifOutValue);
  workbook.getWorksheet("Test").getRange("B2").setValue(ifInvalue);
}

For those who want to learn Office scripting effectively

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

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 Scripts to streamline your workflow.

Click here to buy!!

Links

Office Scripts Articles

Office Scripts(Microsoft Excel)

Office Scripts(Microsoft Excel)