Forum Discussion

yushang's avatar
yushang
Brass Contributor
May 13, 2023

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...
  • JoeUser2004's avatar
    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 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.

     

Resources