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.
In your example the first part is evaluated and because it is FALSE the second part will not be evaluated.