Writing Efficient VBA UDFs Part 14: Handling whole columns using Implicit Intersection – Updated

Excel has an interesting and very efficient trick called Implicit Intersection which allows you to use large Named Ranges and whole column references efficiently.

What is Implicit Intersection?

When Excel expects to get a single cell reference but you give it a range of cells instead, Excel automagically works out the result of intersecting the the range of cells with the row or column of the current cell and uses that. For example:

Entering =A:A in cell B7 does not return the whole of columns A: it returns the intersection of row 7 and column A. Similarly if A1:A20 is named TwentyCells then entering =TwentyCells in B10 does not return all of A1:A20: it returns the intersection of TwentyCells with row 10.

What happens if there is no intersection?

If you enter =TwentyCells in row 30 there is no intersection, so Excel returns #Value.

What happens if you array-enter the formula?

If you array-enter (the Control-Shift-Enter keys all at the same time) the formula you are telling Excel that you want all the values in the range, not just one. So that is what you get. If you only array-enter the formula into a single cell, (for example array enter {=A:A} in cell B5)  then you only get the first of the result values (a is the result of {=A:A} in cell B5).
If you array enter into more than one cell you get more than one result: for example select cells B2:B5, enter =A:A into the formula bar and hit Control-Shift-Enter and B2:B5 will show a b c d.

So how does this work with functions like VLOOKUP?

Usually you give VLOOKUP a single value or reference to use for the lookup value, and a range to use for the lookup table: =VLOOKUP(A4,$A:$C,3,false).
If you give VLOOKUP a range for the lookup value (=VLOOKUP($A:$A,$A:C,3,false) and do NOT array-enter the formula Excel will do the implicit intersection on the lookup value but not on the lookup table.

Implicit Intersection is Amazingly Fast!

Excel has implemented implicit intersection very efficiently: it only passes the single cell reference to the formula or function rather than the whole range.

And only that single cell is treated as a precedent, so the formula/function only gets recalculated when that single cell gets changed/dirtied instead of when any cell in the range gets changed/dirtied.

But UDFs require Special Tricks

Unfortunately ever since Excel 95 implicit intersection does not automagically work for VBA, Automation or XLL UDFs.

But you can still make it happen in 2 different ways:

• Put a plus sign in front of the function parameter
• Use VBA to do the implicit intersection for you

A VBA UDF Example

Function ImplicitV(theParam As Variant) As Variant
ImplicitV = theParam
End Function

When you enter this very simple UDF with a whole column reference Excel pass a reference to the entire column and the UDF has to handle it all: this is slow – on my fast machine it takes 83 milliseconds.

If you add a + sign  Excel only passes the UDF the single cell that is the intersect – this is extremely fast (0.02 milliseconds, over 4000 times faster!).

And the +sign works (very surprisingly) with both text and numbers!

(Thanks to MVP Rory Archibald for pointing this out to me!)

As you can see when you use +$A:$A Excel treats it as an expression and therefore evaluates the expression before passing it to the UDF:

• Evaluating the expression invokes implicit intersection
• Excel does not pass a range to the UDF – it passes the result of the expression

Adding a plus sign works well but you and your users have to remember to do it!

 Using VBA to do the Implicit Intersection

Here is a general purpose VBA function you can call from inside your VBA UDF to do the implicit intersection for you..

Note: fixed 6/October/2016 to handle implicit intersection with a range on a different sheet.

'
' example UDF
'
Function Implicit2V(theParam As Variant) As Variant
Implicit2V = fImplicit(theParam, Application.Caller)
End Function
'
' helper function to hande implicit intersect
'
Function fImplicit(theInput As Variant, CalledFrom As Range) As Variant
'
' handle implicit intersection of an input with a calledfrom range
'
' Charles Williams - Decision Models - 3 october 2016
'
' check for input range
'
If TypeOf theInput Is Range Then
If TypeOf CalledFrom Is Range Then
'
' both input and called from are ranges
'
If Not CalledFrom.HasArray And theInput.CountLarge > 1 Then
'
' called from is not an array formula and the input has more than 1 cell so do implicit
'
' try intersect with row first
'
Set fImplicit = Intersect(theInput, theInput.Parent.Cells(CalledFrom.Row, 1).EntireRow)
'
' if no intersect try column
'
If fImplicit Is Nothing Then Set fImplicit = Intersect(theInput, theInput.Parent.Cells(1, CalledFrom.Column, 1).EntireColumn)
'
' if still nothing return #Value to mimic XL standard behaviour
'
If fImplicit Is Nothing Then fImplicit = CVErr(xlErrValue)
Else
'
' both are ranges but implicit intersect not applicable
'
Set fImplicit = theInput
End If
Else
'
' calledfrom is not a range but input is a range so return a range
Set fImplicit = theInput
End If
Else
'
' input is not a range so return it in a variant
'
fImplicit = theInput
End If
End Function

This is nearly as efficient as using the plus sign (0.04 milliseconds compared to 0.02 milliseconds) – and has the major advantage that you can build it into your UDFs.

The disadvantage compared to the +sign trick is that the whole range is treated as a precent so the UDF will be recalculated whenever ANY cell in the input range gets dirtied or recalculated.

It still works even when when array-entered or when you add the plus sign, but of course that is going to be slow.

Using Range or Object or Double or String as the parameter datatype.

If you use the + sign trick then the UDF parameter has to either be a Variant or Double/String/Boolean type that matches the data type: Range and Object don’t work because Excel always passes the result value rather than a reference.

If you use the fImplicit helper function without the + sign and pass a range then you can use a parameter data type of Variant or Range or Object.

Conclusions

• Using Implicit Intersection with functions can be very efficient
• The + sign trick works well but needs training and remembering to use it!
• A general purpose helper function like fImplicit is fast and more user friendly than + sign