Does Excel evaluate both result arguments supplied to the IF function?

Question

Excel's `if` function takes three arguments, a condition, an if-true value, and an if-false value. Does Excel work out the value of all three arguments, or does it only work out the value of the condition and the corresponding result? Clarification: I'm not wondering what the result of the `if` will be, I'm wondering whether or not it calculates the value of all arguments before calculating the result of the function. This is equivalent to asking whether or not the `if` function uses lazy or strict evaluation. For example, the following pseudocode: ``` x = 5; print x>2 ? "Bigger" : "Smaller" + 1/0 ``` would throw a divide-by-zero exception in a language with fully strict evaluation, as it would evaluate the `1/0`, even though the result wouldn't be required for the `?:` operator. In a lazy-evaluation language, the `?:` operator would evaluate `x>2` before even deciding which expression to evaluate. The problem is that in Excel, `1/0` produces a legitimate value (which happens to be `#…

Accepted Answer

Very east to test

```
? iif(true, 1, 1/0) 'run-time error: division by zero
```

I'm assuming you really mean iif() - in VBA this does not "short-circuit", so you should use `If..Then..Else..End If` in cases where that could be a problem.

Ok - testing what you really asked:

```
'In a VBA module
Function TruePart()
      MsgBox "True part"
      TruePart = "True"
End Function

Function FalsePart()
      MsgBox "False part"
      FalsePart = "False"
End Function
```

In a cell: `=IF(TRUE,truepart(),falsepart())`

Only get one msgbox per calculation of the IF() cell.

As further validation, this gives you two msgbox - one for each:

```
Sub Tester()
    Debug.Print IIf(True, TruePart(), FalsePart())
End Sub 
```

Does Excel evaluate both result arguments supplied to the IF function?

Solution

Problem