Forum Discussion
AND short circuit?
- May 13, 2023
yushang wrote: ``no need to evaluate the second expression. I'm not sure if Excel works this way. Any suggestion?``
You are correct: the AND function evalulates all of its parameters first, then it determines to return FALSE because at least one parameter is false. See the example below.
If you want left-to-right evaluation only as needed, implement nested-IF expressions. For example:
IF(condition1, IF(condition2, "both true", "second false"), "first false")
Note: Do not implement an IFS expression. Like AND, IFS evaluates all of its parameters first, then it determines the first condition that is TRUE left-to-right.
-----
To demonstrate, insert a new VBA module and enter the following functions:
Function myudf1(x)
myudf1 = x
MsgBox "myudf1"
End FunctionFunction myudf2(x)
myudf2 = x
MsgBox "myudf2"
End FunctionThen in Excel, enter 2 into B1 and 2 in C1, and enter the following formula into A1:
=AND(myUDF1(1)=B1, myUDF2(2)=C1)
Note that we see both MsgBoxes for "myudf1" and "myudf2", even though AND will return FALSE because the first condition is FALSE.
-----
Again with 2 in B1 and 2 in C1, enter the following formula in A1:
=IFS(myUDF1(2)=B1, 1, myUDF2(1)=C1, 2, TRUE, 3)
Note that we see both MsbBoxes for "myudf1" and "myudf2", then it returns 1 because the first condition is true.
Disclaimer: I cannot test IFS with VBA UDFs. IIRC, I asked someone to run the test above, and they told me those results. LMK if your results are different.
IF(...) is really a function in Excel. It is roughly equivalent to
Function MyIF(condition As Boolean, value_if_true, Optional value_if_false = False)
If condition Then
MyIF = value_if_true
Else
MyIF = value_if_false
End If
End Function
Another function that will not evaluate all arguments is CHOOSE.
For example, with 1 in B1,
=CHOOSE(B1, myUDF1(10), myUDF2(20))
will only execute myUDF1, and with 2 in B1, it will only execute myUDF2.
With 3 in B1, it will execute neither.