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

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

__, 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 __ all__ of its parameters

__, 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 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.

- HansVogelaarMay 13, 2023MVP
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.

- yushangMay 13, 2023Brass ContributorThank you. got it. add CHOOSE to my list.

- JoeUser2004May 13, 2023Bronze Contributor
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.