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:

implicit1

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.

implicit2

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

implicit3

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.

implicit4

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!)

implicit5

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

 

 

Advertisements
This entry was posted in arrays, Calculation, UDF, VBA, XLL and tagged , , , , . Bookmark the permalink.

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

  1. Alan Elston says:

    Hi Charles Williams,
    I think you have a typo: in section:-
    What happens if you array-enter the formula?
    This I believe is incorrect “……for example array enter {=A:A} in cell B5) then you only get the first of the result values (e is the result of {=A:A} in cell B5)….”
    e is the result for non Array Enter. As you pointed out you will get e due to Implicit Intersection

    For Array Enter the first value in the “Array” will be a
    It might be worth noting that, ( I believe ) , in this case you are not using Implicit Intersection. You will get the same result if you Array Enter {=A:A} in any cell.

    Very interesting Blog. I have often been both intrigued and caught out by Implicit Intersection
    Thank you
    Alan Elston

  2. fastexcel says:

    Thanks Alan: I fixed it

  3. Alan Elston says:

    Hi Charles Williams
    This stuff interested me a bit over the last couple of years. Unfortunately I have no computer background . But I came up with the Idea of “intersections” and “CSE” even before I knew it existed. And long before I knew what CSE meant I used the term CSE as a shortened version of something similar.
    I noticed the effects you talk about in the first third of this blog. Some time later I noticed that pasting a single “breadth” Array over an extended range duplicated the values rather than giving errors as you do get error trying to paste out a two dimensional Array over an extended range. I also later noticed that doing things in single “breadth” Arrays was often extremely quick.
    The way I see it, ( or guess it ), Excel is just a single cell or Range object , or model thereof, and we just see it at different times in the screen spectrum. It is like some sort of Blue Print , or class document. The type of memory locations used are duplicated, or rather based on an offset related to the time we see something on the screen. We visualise all this as a fixed grid.
    Further I am thinking every cell can have, ( once “used” ), effectively a matrix of the same dimensions of a worksheet. This can be used to make available to that cell the values in all cells in the worksheet. ( Other matrixes of similar dimensions hold other things, and there can be many. – that is why the Range object is so big, The “Range” object being how this information is organised. Integral to Excel is the idea of having a Range object as a group of cells as an alternative).
    What I mean is that it is possible to reference all spreadsheet cells from a single cell. I think the way a worksheet single cell reference like, =A1 , gives us an answer is that every value in that matrix is given the value in cell A1. It does this because it recognises that it is a single reference. It does not matter where =A1 is written.

    Let me look in my way at:
    Put = A1 in any cell. It works anywhere. Why? As I believe, all the “available” values in the matrix are given that in the cell A1. On hitting Enter for the single call reference case,. the value is effectively already there to be seen, – actually writing in a cell a single cell reference and hitting Enter, just defines the cell where it will be “shown”. So it is a sort of “instant evaluation”. I am trying to say Excel effectively “holds” a single cell as an entire spreadsheet full with those values

    Now put =A1:B2 in any cell. I think you will find that it errors in any cell. I believe this is because Excel must work differently. It has to work in a sort of Array analysis type way. By seeing a two dimensional reference it does not try to do anything. Excel has just been written that way, or so I am guessing. This is because it needs that other matrix to hold all the values in all cells in the spreadsheet. It does not make any “available” to see. Hitting enter errors because there are no values given. – Any attempt to reveal will hit on an “empty” which is displayed as an error. Excel needs to Control a Shifting in two dimensions to effectively be at the required cell value held in the matrix before doing the evaluation or process that it does on hitting Enter, which “reveals” what is there to see. It does not do that as default. The default is the quick “revealing” of the matrix value of the co ordinate of the “seen” cell position. This is probably done so as to make a single reference very efficient as explained earlier.

    Consider instead now =A1:C1. This is a single “horizontal” . Once again , for similar reasons , this will not work in any cells. However, the Controlled Shifting done before Entering process can be made a bit more efficient. I expect something to aid in the efficiency has in fact been done. I expect the matrix available is filled for all “rows” over a column range of A:C with the values in A1:C1. This allows that the process must only Control its Shift in one dimension before Entering. The use of Hit sets in the default process of revealing the value in the matrix at that relatively seen cell. So if that formula , =A1:C1 , is placed anywhere in column A, ( other than A1 ) , the value in A1 is returned. Similarly if that formula , =A1:C1 , is placed anywhere in column B, ( other than B1 ) , the value in B1 is returned.
    Similarly if that formula , =A1:C1 , is placed anywhere in column C, ( other than C1 ) , the value in C1 is returned. I never thought of this as a “Implicit Intersection” , but I suppose that description suits it as well as my “Intercept” idea.

    Your example uses a single entire column reference =A:A . The matrix I am talking about, ( the one whose values in it are revealed by area selected before hitting the Enter ) , is therefore filled with the row values of column A in all columns. That reference , =A:A , in any cell in any column ( except in column A ) will return the value in that cell row in column A, as this has been entered into every column in the matrix.

    Controlled Shifting before Enter
    Any of the above references will work with the CSE stuff as you have explained. I think that is consistent with my Controlled Shifting before Enter idea. I think that it also does a sort of “breaking of the link of the intercept to a cell”, which is required for the simpler process done by default on hitting Enter.
    Also if you highlight the reference and Hit F9 some other process works, I think, may be something called a “Evaluate engine”. It returns the Range object, which defaults to the value.
    ( Evaluate(“ “) is done independent of the cell – it may reference cells but the command line is not in a cell. We do not have a process that “reveals”. We will receive our answer as either a single or collection (field) of values. We must do some additional programming steps to “reveal” that information)
    Alan

  4. Alan Elston says:

    Hi again.
    VLOOKUP example
    I am not quite sure what you are demonstrating with your VLOOKUP example. As I see it, because of “Intercepts”, or “implicit intersection” or whatever we chose to call it, your formula, VLOOKUP($A:$A,$A:C,3,false), is working as if it were this formula in cell E1 and dragged down:
    VLOOKUP($A1,$A:C,3,false).
    Possibly you are saying something like…”…As in any such formula, the argument that takes a matrix ( Array or range ) is a fixed Array. It is independent of any other processes which may come into play when , for example ,using a matrix ( Array or range ) for an argument that normally takes a single value. Here the process is the of “Intercepts”, or “implicit intersection” or whatever we chose to call it. Possibly as the formula gives the same as a CSE entered formula you may be suggesting it would be more efficient than a CSE entered formula as the extra ..”..Controlled Shifting before entering to obtain the answer before hitting Enter..”.. process is not required?
    I think often a formula will be used in such a way to give the same results as if it were CSE entered. I think that is indeed what you are saying with : ….”… 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…..”
    That is very interesting..

    Alan

  5. Alan Elston says:

    Hi again.
    VBA and “implicit intersection”
    Regarding your VBA examples. I am not quite sure what you are demonstrating there or trying to achieve.

    ( BTW: The following all returned an error in my Excel initially _..
    =ImplicitV($A:$A)
    { =Implicit2V($A:$A)}
    {=Implicit2V(+$A:$A)}
    _..That is to say, I tried to do your examples from the screenshot, and the formulas in B4 B16 and B19 all errored in my Excel. I expect that could be a memory type problem on my machine to some extent: If I reduce the column depth, ( so use for example $A1:$A30 ), then I get the results , but I do not really understand what you are doing with Implicit2V and fImplicit )

    As for ImplicitV in B4
    I expect possibly that the formula in B4 is not doing “Intercepts”, or “implicit intersection” because it does not get a chance. You could think of it as doing the first part of “..Controlled Shifting before Enter process..”. – The reference has been “lifted” from the worksheet initially, which I expect is internally what sets that process into action in “..Controlled Shifting before Enter process..”..
    You will find that the formula from B4 will behave as a CSE entered formula in any cell or cells in the worksheet. But I do not think it has anything to do with VBA and “implicit intersection”. – This is what I see is happening:
    This is actually what your Function is doing I think:
    The Range object is taken in. By assigning a Variant variable to a Range objet, VBA defaults to putting the .Value into the Variant variable. For the case of a Range object of more than one cell, .Value defaults to that of the first area of the Range object. A field , ( Array ) , of member types of Variant is retuned.
    Your theParam defaults to a Range object. In your code line, theParam defaults to theParam.Value. And ImplicitV becomes an Array of the .Values held in Variant type member elements. Your Function ImplicitV effectively assigns that entire Array of values to a single cell. So you only get the top left value from the Array displayed. In a way you are doing a sort of CSE Type 1 to put the Array of values in.

    More explicitly the same Function (ImplicitV ) is like this, I think:
    ‘ Code in Normal Code module
    Public Function CSE1PasteRangeValuesIn(RngIn As Range) As Variant ‘The Function takes in a Range and makes an Array of its values. The only variable that can hold an Array is Variant. Hence the Function, which is what is returned, must be a Variant type
    Dim arrOut() As Variant ‘ The .Value property is used below to return (present) a field of values from the Range. The Member Elements are held as Variable types
    Let arrOut() = RngIn.Value
    Let CSE1PasteRangeValuesIn = arrOut()
    End Function

    BTW: As it is a function it can only change the single cell and no other cells in the worksheet, so it is a bit of a coincidence that this “works” to give any results at all.

    ‘Trying to do the same for a CSE type 2 Entry, ( so as to get all the values displayed ) would need , for example a more complicated set of codes, due to the problem of a User Defined Function not being able to alter values in a worksheet other than the cell in which the Function is written. ( I think ).
    ‘Something like this would be needed possibly:
    ‘The Public variable and two routines below need to go in a Normal Code Module
    Public GlobyVararrOut As Variant ‘ Use Variant so as to be able to do an IsArray( ) check and set to Empty for Empty check
    Public Function CSE2PasteRangeValuesIn(RngIn As Range)
    Let GlobyVararrOut = RngIn.Value ‘values Array from Range selected in function written in Worksheet
    End Function
    ‘Code below is scheduled to run 5 seconds after above Function ends by Worksheets_Change in worksheet code module it pastes out the values Array for the range taken in in the Function call in a worksheet cell
    Sub ControlledShiftedToGlobyEnterIt()
    If IsArray(GlobyVararrOut) Then ‘Check for Array filled by above Function
    Dim RngOut As Range ‘variable for Range object of range to use for Array output in worksheet
    Set RngOut = ActiveCell.Offset(-1, 0).Resize(UBound(GlobyVararrOut, 1), UBound(GlobyVararrOut, 2)) ‘ Onne back from where a Hit Enter took me then resized to dimensions of output Array
    Application.EnableEvents = False ‘ To stop Worksheets_Change code kicking in on filling range
    Let RngOut.Value = GlobyVararrOut ‘Fill range with values
    Application.EnableEvents = True
    Else ‘ No filled Array – Function above was not run
    End If
    Let GlobyVararrOut = Empty ‘Empty variable for check to work if Worksheet change not cause by above Function
    End Sub

    ‘ This Code below must go in the Worksheets Code Module of the worksheet where the function is written in a cell. It schedules the last Sub routine above to run, which pastes in the values Array which is held in a global variable.
    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.OnTime Now + TimeValue(“00:00:05”), “ControlledShiftedToGlobyEnterIt”
    End Sub

    So the idea above for a CSE type 2 like demo is to put those codes in a workbook, then in the worksheet in whose code module last code is in, you put some values in the first column, A, such as in your first screenshot, and then type something like this in any cell:
    =CSE2PasteRangeValuesIn(A4:B6)
    You should then get this, ( staring at top left of where you typed the formula in )
    d
    e
    f
    But once again, this has nothing to do with “Intercepts”, or “implicit intersection”.
    _……………………

    It makes sense to me that doing this “”&$A:$A as an alternative to the + trick, which also works , does an immediate evaluation somehow:
    Then I guess just as in a cell, it will return the value for the given reference, which in this case “reveals” the available row value from the “Intercepts”, or “implicit intersection” thing.
    ( I am not quite sure why the + works. Sometimes in string building a + can be used in place of a & . RoryA appears to be a brilliant hack, but I wonder if he understands his great solutions, Lol!! ).
    You are not, I think, doing any “Intercepts”, or “implicit intersection” in VBA in this case. Your Function takes a value from a worksheet an puts it back in. I do not think that has anything to do “Intercepts”, or “implicit intersection” and VBA. As you showed yourself, VBA, is just taking a string value and giving it back. I see no relevance to “Intercepts”, or “implicit intersection” and VBA. It seems like the “Intercepts”, or “implicit intersection” is done before the function starts.
    You are just , I think, demonstrating “Intercepts”, or “implicit intersection” in a spreadsheet in this case

    These Functions will give me results like in “Intercepts”, or “implicit intersection” also
    Public Function EvaluateRange(theParam As Range) As Range
    Set EvaluateRange = Evaluate(“” & theParam.Address & “”)
    End Function
    Public Function EvaluateRangeStr(theParam As String) As Range
    Set EvaluateRangeStr = Evaluate(“” & theParam & “”)
    End Function
    But this is inefficient I expect. It takes a Range object or string of such, uses the Evaluate to return that Range object and puts it back in.
    Possibly this Function effectively also breaks the cell link process of default got when using Enter. Possibly this evokes the Controlled Shifting before Entering process. But I do not think it is demonstrating VBA and “Intercepts”, or “implicit intersection”

    I think I am confused as to what you mean be implicit intersection for VBA.
    In my way of thinking, “Intercepts”, or “implicit intersection” is a general characteristic of Excel which results from how it handles single breadth ranges or Arrays in any analysis or evaluation, be it in VBA or Excel.
    If for example you use Evaluate(“ “) in VBA on a reference that errors in a spreadsheet without CSE , such as this_..
    Evaluate(“=A1:B2”)
    _.. you will find that it does not error but returns the Array of values , like Range(“A1:B2).Value.
    I expect that Evaluate does some similar things to how a things work in a spreadsheet cell. But it is not quite doing the same. It is not following my explanation of “Intercepts”, or “implicit intersection”. Possibly it always does a Controlled Shifting before Entering type process to access a call value from the spreadsheet. It does not have the extra Matrix which I suggested a cell has which can be utilised to speed up both a single cell and a single breadth evaluation. That extra process when being done for a single breadth reference is , possibly as a side effect rather than design, leading to the “Intercepts”, or “implicit intersection” phenomena.

    I do not understand this statement: ..”… ever since Excel 95 implicit intersection does not automagically work for VBA…”..

    I do use a technique involving the use of “Intercepts”, or “implicit intersection” in VBA. For example here : ( But it is still not directly demonstrating “implicit intersection”. It is demonstrating how Excel “holds” single and single “breadth” range references, which I am guessing is part of how Excel “works” which leads to spreadsheet “implicit intersection”.
    [size=1] excelforum.com/showthread.php?t=1099995#post4571172
    usefulgyaan.wordpress.com/2013/06/19/avoid-loop-for-range-calculations-evaluate/#comment-737
    mrexcel.com/forum/excel-questions/908760-visual-basic-applications-copy-2-dimensional-array-into-1-dimensional-single-column-2.html#post4375354 [/size]
    Alan

  6. fastexcel says:

    Alan,
    Some points where I believe Excel is implemented in a different way to your theories..
    1. Excel internally uses a single sparse matrix cell table to hold values and formulas and formats etc. Cells that do not have any value/formula/formatting do not exist in the cell table. Each cell in the cell table holds a single value. A formula reference in a cell is an instruction to the Excel calc engine to go find the value(s) form the cells referred to in the formula.
    2. A VBA UDF that is array-entered into multiple cells can return values to all the cells it is array-entered into, so it is not correct to say that a VBA UDF can only return a single value.
    3. In fact It is more accurate to say that a VBA UDF can always return an array but Excel ignores all values except for those that correspond to the cell(s) containing the UDF You can see this by putting a breakpoint on the End Function line in ImplicitV and inspecting ImplicitV in the Locals window.
    4. AFAIK Excel resolves non-array entered implicit intersection references to single-cell references (where possible) in the dependency trees when the formula is entered. You can see this effect if you use trace precedents. and its the most obvious explanation for the lightning fast calculation of implicit refs.

    • Alan Elston says:

      Hi
      Thanks for the reply
      To your points:
      _1. At first read this seems actually to tie up my theories. As I am not privy to exactly what is going on, I am trying to suggest some memory location system which somehow based on the cell under considerations is sent via a specific offset somewhere. This could just as well be considered a matrix of actual values. I may have gone off course a bit confusing which values matrix I was talking about. My imaginary “help matrx” will be somehow just the values (s) needed for a formula. I can accept it is somehow organised such that what I might consider as empty could through some computer organising system be actually nonexistent. That make sense. Thanks for that. I will add that to my “Theories” 🙂
      I am thinking that in the case of any 2 Dimension range reference that Excel calc engine goes find the values form the cells referred to in the formula in a matrix. My suggestion was that a help matrix is filled in differently such that the second time around, such as in when the spreadsheet updates, a single “breadth” reference only needs to go in one direction, and that a single cell it sort of goes nowhere. ( Or rather it is already at a pseudo location, by virtue of “where” the cell under consideration is).
      It is all somehow integral to the way that Excel works. Just as when I paste a single value out into a large 2 dimension spreadsheet area I get the value repeated. If I do the same with a row, then the row is repeated. If I try to paste out a small area over a large area, then I only get the single area
      It is very difficult to get across my thinking. I am thinking it evaluates a single and multiple reference a bit differently.
      I expect at the end of the day exactly what is going on in Excel is a trade secret, so any theory that can predict the results is valid. I have a feeling we are not too far apart on the thinking here.

      _ 2. 3. I am fully in agreement with you here. That is integral to what CSE type 2 entry is about. ( By type 2 I am referring to selecting an extended range before CSE to “take” the output ). My comments were restricted to the single cell non CSE entry case.
      In the CSE type 2 case Excel is doing here its usual job of just doing the calculation a few times and returning the result in an Array which the selection before CSE has effectively given as the area to output to. I fully agree that in such a way a UDF can change more than one cell in a worksheet. Sorry I was not clear as to what I was referring to there.

      _ 4. I am afraid I cannot comment in detail there. My lack of computer knowledge is coming in unfortunately. However a quick google suggested it might approximately be tying up with some of my thinking. You may have said in _4. In more technical terms what I was trying to explain in simple terms that I can understand..
      This: “….”….dependency trees when the formula is entered….”… seems to be saying how I am saying that the cell or cells being “looked at” influences what is seen. This in turn explains the cell position dependence of Implicit interception.
      ( What you said .. “Where Possible” .. is also ties up approximately , I think, with my thinking. I might be saying that for a 2 Dimensional range reference it might not try in the first place
      _……………….

      Sadly, I was not able to follow at all what Implicit2V or fImplicit was about. As I tried to explain I think what was happening with ImplicitV can be explained by things that have nothing directly to do with Implicit Intersection. I think I am following your reasoning with the VLookUp example. But I cannot see any advantages over using a simple formula dragged down so that the Look Up value is a simple cell reference. ….Or maybe I can when I re think now…: Possibly there are some memory advantages based on how the different ways , ( by different ways I mean .. Many formulas with different Look Up value versus a Single CSE formula ). I know from other formula work and Forum Thread participation that Excels can have some strange speed issues effected by size of memory used in filing cell values. I expect the formula used by Array entry is held in a different way as it is effectively the “same string in the cell”, somehow.. I would be interested therefore in a speed test comparison to include the simple single Look Up value formula in VLookUp.
      The speed tests you did on VBA and Implicit Intersection were lost on me as I either could not understand your functions, or those that I could appeared not to be testing directly Implicit Intersection.
      _…..

      Thanks again for both blogging and taking the time to reply. Sorry the posts were a bit long. I have not seen many blogs at all on Implicit Intersection. So this one of yours really caught my eye. Many THANKS
      Best wishes.
      Alan

  7. Alan Elston says:

    A minor last point on the VLookUp, having just rethought. If I use non CSE
    VLOOKUP($A:$A,$A:C,3,false), instead of the usually non CSE formulas in a few rows like,
    VLOOKUP(A1,$A:C,3,false)
    VLOOKUP(A2,$A:C,3,false)
    VLOOKUP(A3,$A:C,3,false)
    Then possibly I have no speed advantages . My explanation of what Excel was doing suggested that a single cell reference did a “sort of” Implicit intersect. Once again it is just saying this possibly in a different way….” Excel resolves non-array entered implicit intersection references to single-cell references (where possible) in the dependency trees when the formula is entered…..”
    I think “…dependency trees….” and my imaginary help matrix full of values, all with the same for a single reference ( and all columns holding the same row value for a single row reference ), are possibly amounting to the same thing.
    Alan

  8. Jeff Weir says:

    Charles: very useful post…I had need of this today and wasn’t surprised to see Google lead me here. Thanks.

    • alanelston says:

      Hi Jeff,
      Just out of interest, How ( if ) are you using Implicit Intersection?
      I find it quite interesting academically,
      excelforum.com/tips-and-tutorials/1172587-excel-vba-interception-and-implicit-intersection-and-vlookup.html
      excelfox.com/forum/showthread.php/2145-Excel-VBA-Interception-and-Implicit-Intersection-and-VLookUp
      But have not found a good example of its advantages over normal formula entry . ( I was not able to follow the speed advantages discussed here 😦 )
      Alan

    • alanelston says:

      Hi Jeff,
      Just out of interest, How ( if ) are you using Implicit Intersection?
      I find it quite interesting academically,
      ( Excelforum tips-and-tutorials/1172587-excel-vba-interception-and-implicit-intersection-and-vlookup
      Excelfox forum/showthread.php/2145-Excel-VBA-Interception-and-Implicit-Intersection-and-VLookUp )
      But have not found a good example of its advantages over normal formula entry . ( I was not able to follow the speed advantages discussed here 😦 )
      Alan

      • Jeff Weir says:

        Hard to explain, but mainly for aesthetic purposes. I use it in conjunction with some VBA code that generates dynamic named ranges for PivotTables…something I discuss at the following guest post:
        http://chandoo.org/wp/2014/10/18/introducing-structured-references-for-pivottables/

        In particular, I have a FuzzyMatch User Defined Function (i.e. a custom-built worksheet function) that is trying to match names from a PivotTable that might be misspelt with a list of names in a database. Without Charles’ implicit intersection, my formula would look like this:
        =FuzzyNameMatch(F13,G13,FirstNameLookupList,LastNameLookupList)

        I don’t like using ‘naked’ cell references like F13, G13, because they don’t have any intrinsic meaning. You don’t know what F13 and G13 mean without going to those cells and seeing if there is some kind of column header that tells you what they relate to. (F13 is someone’s first name, G13 is someone’s last name, and the FirstNameLookupList & LastNameLookupList arguments after that are the arrays containing the ‘official’ first and last names that I’m looking in for a match).

        My PivotNames code I reference above generates dynamic named ranges with meaning…in this case, a name called FirstNameInput and LastNameInput. But I can’t pass them to my original FuzzyNameMatch UDF, because that UDF is expecting just one FirstName/LastName pair to look for a match for, and not an entire array. But implementing Charles’ Implicit Interseciton code allow me to replace those F13 and G13 inputs with dynamic named ranges that point at the entire PivotTable using the custom names I generate for them:
        =FuzzyNameMatch(FirstNameInput, LastNameInput, FirstNameLookupList, LastNameLookupList)

        There’s no benefit to me apart from greater understanding: In 12 months time when I have to do something to this formula, I will know exactly what this means, whereas if I was looking at the alternative:
        =FuzzyNameMatch(F13,G13,FirstNameLookupList,LastNameLookupList)
        …I might struggle to remember what is supposed to be in F13, and what is supposed to be in G13.

  9. alanelston says:

    @ Jeff
    Hi Jeff,
    Thanks for the reply.
    I have a little experience with Tables but no idea at all about Pivot Tables.
    So it is a bit difficult for me to follow your info.
    But I will certainly review this again later.
    ( Strangely I stumbled upon a few more , or rather got some more suggestions on a Google search for Implicit Intersection Today, for the same search I found nothing on previously.. ( Google is eerie and frightening sometimes like that ). ).
    So I have some more to look at now.

    Thanks again for taking the time to reply

    Alan

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s