Forum Discussion
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 expression. I'm not sure if Excel works this way. Any suggestion? Many thanks!
=AND(1=2,1=1)
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.
12 Replies
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.
More exactly, IFS evaluates not ALL conditions, but ones till first TRUE appears and ignores the rest.
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).
- JoeUser2004
I have a different impression for IFS functions. I agree AND function does not do short circuiting. But I think behaviour of IFS function is a bit different with respect to VBA function call vs excel native function/operation. Here is how I checked using both native operation and your VBA function.
AND function:
Quick and dirty using native operation: If I use a formula "=AND(FALSE, 3/0)" it returns #DIV/0!
VBA function call formula as "=AND(myudf1(1)=2, myudf2(2)=2) both MessageBox appear
IFS function:
Quick and dirty using native operation: If I use a formula "=IFS(TRUE,1,3/0,2)" it returns 1 bypassing evaluation of second condition.
VBA function call formula as =IFS(myudf1(2)=2, 1, myudf2(1)=2, 2, TRUE, 3): both MessageBox appear.
But I don't know the reason for the differences. I might be missing something. Thankssanjibdutta wrote: ``If I use a formula "=IFS(TRUE,1,3/0,2)" it returns 1 bypassing evaluation of second condition. VBA function call formula as =IFS(myudf1(2)=2, 1, myudf2(1)=2, 2, TRUE, 3): both MessageBox appear. But I don't know the reason for the differences.``
First, thanks for confirming my assertion about IFS, namely ``both MessageBox appear``.
As for the difference with respect to the #DIV/0 error.... As I noted in a previous response (after yours), even though IFS and SWITCH (et al?) evaluate all parameters before selecting the result, any Excel error is suppressed unless and until it is selected.
FYI, the same is true of a VBA function with 2 or more parameters -- if the parameters are type Variant, and the errant parameter is not referenced (other than IsError).
For example:
Function myudf(cond As Boolean, a As Double, b As Variant) As Double
If IsError(b) Then MsgBox "myudf error" Else MsgBox "myudf"
If cond Then myudf = a Else myudf = b
End FunctionIn Excel:
=myudf(true, 1, 1/0) returns 1 despite #DIV/0 error in the 3rd parameter
=myudf(false, 1, 1/0) returns #VALUE due the #DIV/0 error in the 3rd parameter
In both case, the MsgBox displays "myudf error", which demonstrates that Excel itself does not recognize the error in the 3rd parameter.
- So, IF is the only Excel function that has the same semantic of "if" in most programming languages? I even doubt whether Excel IF is really a function.
yushang wrote: ``IF is the only Excel function that has the same semantic of "if" in most programming languages? I even doubt whether Excel IF is really a function``
Again, you are correct. Excel IF is more like an operator than a function.
-----
HansVogelaar wrote: ``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)``
I disagree. A true function would evaluate all of "condition", "value_if_true" and "value_if_false" before invoking the function "MyIF".
In contrast, the Excel IF operator evaluates "condition". Then only if "condition" is true is "value_if_true" evaluated. And only if "condition" is false is "value_if_false" evaluated.
This is important if the "value_if" parts might call UDFs or if they might be "expensive" (e.g. complicated searches and calculations).
-----
HansVogelaar wrote: ``Another function that will not evaluate all arguments is CHOOSE``
I agree.
In your example the first part is evaluated and because it is FALSE the second part will not be evaluated.
- Yes. If anyone of logical expression is false means, the result will be false.
