May 12 2023 06:24 PM
May 12 2023 06:24 PM
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!
May 12 2023 11:36 PMSolution
@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:
myudf1 = x
myudf2 = x
Then in Excel, enter 2 into B1 and 2 in C1, and enter the following formula into A1:
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.
May 13 2023 03:26 AM
May 13 2023 03:57 AM
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.
May 13 2023 04:38 AM
May 13 2023 06:46 AM - edited May 13 2023 07:47 AM
@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.
@Hans Vogelaar 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).
@Hans Vogelaar wrote: ``Another function that will not evaluate all arguments is CHOOSE``
May 13 2023 06:49 AM - edited May 13 2023 07:14 AM
@Sergei Baklan 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).
May 13 2023 07:12 AM - edited May 13 2023 07:45 AM
@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).
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
=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.