Short-circuiting in Array Formulas

The previous post demonstrated that IF and CHOOSE short-circuit but IFS and SWITCH do not.

But following up a hint from Bill Wu that IF behaves differently in array formulas I decided to check whether it still short-circuits.
(IF in array formulas currently only returns a maximum of 255 characters but non-array IF does not have this limitation)

The short answer is that IF and CHOOSE do not short-circuit in array formulas but use a different and very efficient trick. So lets see how they work.

I am using the same VBA Pass UDF as in the previous post but using an array instead of a single value as the first argument to IF.

Option Explicit

Function Pass(valu As Variant)
    Pass = valu
    Debug.Print valu
End Function

The results in the immediate window are slightly surprising:

So what is Excel doing here?

The standard way Excel handles functions is to first evaluate each of the function arguments and only then call the function and pass it the results of the argument evaluations.
When the IF is an array function instead of doing short-circuiting it works in the standard non-short-circuiting way:

• B5:B8 evaluates to an array containing False, False, True, False
• Pass(80) evaluates to 80 and puts 80 in the Immediate window.
• Pass(90) evaluates to 90 and puts 90 in the Immediate window
• Then the IF function is called with these values.

Excel knows that it has to return 4 values so creates a 4 cell output array and then calls the IF function 4 times passing it a single value from the True/False array and the 80 and 90 result of evaluating the Pass functions. Excel puts the results of the 4 IF calls into the output array and finally returns the array to the grid. This array process is called “Lifting”

This lifting process is very efficient because the 2 Pass() arguments only get evaluated once rather than 4 times.

What about CHOOSE, IFS and SWITCH?

The same lifting process happens for array formulas of CHOOSE, IFS and SWITCH (and indeed all other functions that are not natively array capable).

255 character limit in Lifting?

I wonder if the reason for the 255 character limit is that Excel has to create the output array before calling the function so does not know what the maximum return size is, and for efficiency reasons creates fixed length array items which then get appropriately trimmed when returning to the grid.