Forum Discussion
yushang
May 13, 2023Brass Contributor
AND short circuit?
Hi guys, I'm not sure if AND support short circuit, for example, when evaluate the first expression we know that the whole expression will be false, so, there is no need to evaluate the second expre...
- 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.
JoeUser2004
May 13, 2023Bronze Contributor
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 Function
Function myudf2(x)
myudf2 = x
MsgBox "myudf2"
End Function
Then 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.
SergeiBaklan
May 13, 2023MVP
More exactly, IFS evaluates not ALL conditions, but ones till first TRUE appears and ignores the rest.
- JoeUser2004May 13, 2023Bronze Contributor
SergeiBaklan wrote: ``IFS evaluates not ALL conditions, but ones till first TRUE appears and ignores the rest``
That is not what I have been told or read. I provided a method to test the behavior. Please show us the results. Unfortunately, I cannot.
EDIT.... No need. sanjibdutta already demonstrates that what I wrote is correct. I don't know why you ignored it 2 hours later.
PS.... That is also a problem with the new SWITCH function, IIRC. It evaluates all of its parameters before selecting the result to return. (TBD)
-----
EDIT.... An important difference with IFS and SWITCH is: even though all parameters are evaluated (I believe), the function does not through an error like #DIV/0, #VALUE or #NUM unless that parameter is selected. (TBD)
That much can be demonstrated easily using Excel Online (onedrive.live.com).
- SergeiBaklanMay 13, 2023MVP
My comment was to this
Like AND, IFS evaluates all of its parameters first, then it determines the first condition that is TRUE left-to-right.
You may compare
=IFS(FALSE, 1, 2=2, 2, 1/0, 3, 3=3, 4)
and
=IFS(FALSE, 1, 1/0, 3, 2=2, 2, 3=3, 4)