## 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

of its parameters*all*, then it determines to return FALSE because at least one parameter is false. See the example below.*first*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

of its parameters*all*, then it determines the first condition that is TRUE left-to-right.*first*-----

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

MsbBoxes for "myudf1" and "myudf2", then it returns 1 because the first condition is true.*both*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.

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. Thanks

sanjibdutta 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 Function

In 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.