INDEXLS to index Linked Spills in foreign Workbooks

[ecovonrein]

The problem:

Large production environments will consist of more than one Workbook where one processing Workbook(1) will pull back data from other Workbooks(2..n).  When Workbook(1) is published to interested outsiders, the links to Workbooks(2..n) are broken and Excel freezes the imported values.  Workbook(1) no longer updates but can now operate in a foreign environment on a stand-alone basis. So far, so familiar. 

Now, the most obvious way to pull back data into Workbook(1) in modern Excel is by means of Spills.  And we would obviously reference these imported Spills using Excel's "#" notation. 

Bad idea.  REALLY BAD IDEA.  When Excel converts the Spills into frozen numbers, it does not patch up related Spill references.  As a result, the broken Workbook(1) will show #REF!s all over the place.  I consider this behaviour a bug of Break Links.

The work-around I used everywhere is to spill these data ranges into oversized areas which I reference using fixed conventional range notation spanning the entire oversized range.  There are two problems with this work-around.

1 I must make sure that when data spills over the receiving fixed range that the importing Spill formula errors out.  That isn't too hard.  However, it is fairly irritating to chase #SPILL! errors thru a large Workbook to find the offending range and manually expand it (some more).

2 All formulas accessing the receiving space must be programmed to tolerate blank cells.

A proposition:

Today, I thought of a new work-around.  It comes too late for me to roll it out across my current project.  (It will have to wait until next year for an update.)  However, I publish it here in case it helps fellow travellers today.

The solution is based on two very simple VBA functions.  While UDFs are generally heavily circumscribed as to what side-effects they may have on a Workbook, I was surprised to find that a UDF may affix a Note to a cell.  And I already knew that a UDF can conjure up a range out of nowhere.  (Mind that calculation tree!)

An idea was born.  If I imported all linked Spills via a UDF (I called importSpill) that each time it recalcs (ie fetches new data from a linked Workbook) attaches the dimensions of the received data range in a Note to the top left corner (TLC), then I could write an INDEX-equivalent (I called INDEXLS) that, in the event that the Spill reference fails (suggesting that the link to the source had been broken), could derive a regular range out of TLC given the dimensions contained in the Note.

The VBA code:

Option Explicit

Public Function INDEXLS(TLC As Range, r As Variant, ri As Long, ci As Long) As Variant
    ' This function should only be called when r references a Spill created by importSpill
    ' from a Spill range in another Workbook.    When the link to that Workbook is broken,
    ' Excel will freeze the Spill, invalidating the Spill notation r# in the process.  TLC
    ' must reference r(1,1) only, ie the same as r MINUS the "#".  When r arrives as #REF,
    ' we know hence that a note  attaches  to TLC in which importSpill recorded the dimen-
    ' sions of the original Spill, allowing the code to find the BRC of the now frozen set
    ' of numbers.
    
    ' A note on the calculation tree:
    ' To make this code work does not strictly require the input r#. However, if r# is not
    ' supplied into IndexLS while the Spill is active (ie the link to the foreign Workbook
    ' is live), any changes in r to cells other than TLC will NOT fire INDEXLS !!
    
    If IsError(r) Then
        Dim crumbs As Variant
        crumbs = Split(TLC.Comment.Text, "x")
        
        Dim BRC As Range
        Set BRC = TLC.Worksheet.Cells( _
            TLC.Row + crumbs(0) - 1, _
            TLC.Column + crumbs(1) - 1 _
        )
        
        Set r = Range(TLC, BRC)
    End If
    
    ' Performing some gymnastics so that INDEXLS can slice and dice ex-Spills
    If ri = 0 Then
        If ci = 0 Then
            INDEXLS = r
        Else
            INDEXLS = r.Columns(ci)
        End If
    Else
        If ci = 0 Then
            INDEXLS = r.Rows(ri)
        Else
            INDEXLS = r(ri, ci)
        End If
    End If
End Function

Public Function importSpill(aSpill As Range) As Variant
    With Application.ThisCell
        If Not .Comment Is Nothing Then .Comment.Delete
        .AddComment aSpill.Rows.Count & "x" & aSpill.Columns.Count
    End With
    
    importSpill = aSpill
End Function

INDEXLS can help beyond retrieving values at the intersection of ri and ci.  Where I might today reference an imported Spill out of AA100 in VLOOKUP, say, via a fixed oversized range

VLOOKUP(someValue,AA100:BA120,2,FALSE)

I could now write

VLOOKUP(someValue,INDEXLS(AA100,AA100#,0,0),2,FALSE)

Thoughts / feedback welcome.

[#2]

@ecovonrein 

It looks like you have provided a detailed description of a creative solution to handle issues related to linked spills in Excel. Your approach involves using VBA functions to work around the limitations of Excel's spill behavior when dealing with linked data from other workbooks. The proposed solution uses two user-defined functions (UDFs), INDEXLS and importSpill, to achieve this.

While your approach appears to address the specific challenges you've outlined, it's important to note that working with UDFs and manipulating cell comments might introduce some complexity to the workbook, and not all users might be comfortable with or have access to VBA.

Here is a brief summary of your approach:

1. importSpill Function: This function takes a range (aSpill) as input and attaches a comment to the top left cell of the range. The comment contains the dimensions of the original spill. The function then returns the input range itself.
2. INDEXLS Function: This function is used to retrieve values from a linked spill range. If the linked spill range (#REF error) is frozen, it reads the dimensions from the comment attached by importSpill and constructs a regular range. It then returns the value at the specified row and column index within that range.

Your solution seems well thought-out and provides a method to work with linked data more effectively while handling the challenges you have encountered. It demonstrates creative thinking and leveraging VBA to overcome Excel's limitations. Just keep in mind that using custom VBA functions might impact workbook performance and compatibility with other users who might not be familiar with VBA.

As with any complex Excel solution, thoroughly testing the solution with different scenarios and use cases will help ensure its reliability and effectiveness. If the solution meets your needs and improves your workflow, it can be a valuable addition to your Excel toolkit.

Your VBA code seems well-structured and thoughtful. However, I noticed a small issue and have a couple of suggestions for clarity and error handling. Here is the modified code:

vba

Option Explicit

Public Function INDEXLS(TLC As Range, r As Variant, ri As Long, ci As Long) As Variant
    ' This function should only be called when r references a Spill created by importSpill
    ' from a Spill range in another Workbook. When the link to that Workbook is broken,
    ' Excel will freeze the Spill, invalidating the Spill notation r# in the process.
    
    ' If r is an error, retrieve the dimensions from the Note and create a regular range
    If IsError(r) Then
        Dim crumbs As Variant
        crumbs = Split(TLC.Comment.Text, "x")
        
        Dim BRC As Range
        Set BRC = TLC.Worksheet.Cells( _
            TLC.Row + crumbs(0) - 1, _
            TLC.Column + crumbs(1) - 1 _
        )
        
        On Error Resume Next
        Set r = Range(TLC, BRC)
        On Error GoTo 0
    End If
    
    If Not r Is Nothing Then
        ' Performing some gymnastics to slice and dice ex-Spills
        If ri = 0 Then
            If ci = 0 Then
                INDEXLS = r.Value
            Else
                INDEXLS = r.Columns(ci).Value
            End If
        Else
            If ci = 0 Then
                INDEXLS = r.Rows(ri).Value
            Else
                INDEXLS = r(ri, ci).Value
            End If
        End If
    Else
        INDEXLS = CVErr(xlErrNA)
    End If
End Function

Public Function importSpill(aSpill As Range) As Variant
    With Application.ThisCell
        If Not .Comment Is Nothing Then .Comment.Delete
        .AddComment aSpill.Rows.Count & "x" & aSpill.Columns.Count
    End With
    
    importSpill = aSpill
End Function

Changes and Improvements:

1. Error Handling: Added On Error Resume Next and On Error GoTo 0 around the creation of the range from TLC to BRC in case it fails. This ensures that if the range creation fails, it will not cause a runtime error and will continue processing.
2. Value Property: Used the .Value property explicitly when retrieving values from the ranges. This helps avoid unnecessary complications related to handling the Range object.
3. Error Handling for No Range: Added an Else section to handle cases where the range creation fails (likely due to exceeding worksheet limits). In this case, the function will return a #N/A error, indicating that the value is not available.

These changes should enhance the reliability and error handling of your code. Make sure to test the modified code with various scenarios to ensure it performs as expected. The text and steps were created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

[#3]

Thanks! " The text and steps were created with the help of AI." I thought so. My son bounced some of my code off ChatGBT once and the answer he got was pretty much in the style of your post :)

"not all users might be comfortable with or have access to VBA." That is true and is sadly the price we pay for working around Excel bugs. And I consider it a bug since Excel knows fine well at the point of breaking the link what the address span of the range is whose values it is freezing. It also knows all dependencies of the TLC, so could (should!) hence patch up any # references to it.

[#4]

I had a further thought.

I was overthinking the calculation tree.  On reflection, one feature of Spills is that their elements are either ALL dirty or ALL clean.  It cannot be, as I worried in the OP, that only r(1,4) is dirty.  If the source Workbook refreshes r(1,4), then importSpill will fire and the entire range spilling out of TLC will be dirty, including TLC - hence assuring that INDEXLS would fire.

Moreover, rather than reinvent INDEX, I can simply re-imagine the "#" (as a UDF called "LS"):

Public Function LS(TLC As Range) As Variant
    On Error GoTo BAIL_OUT
    
    ' This function should only be called when r references a Spill created by importSpill
    ' from a link to another Workbook.  TLC must reference r(1,1)  of that data range (but
    ' NOT feature a trailing "#"). If a range spills out of TLC, LS will simply refer
    ' to it (as if TLC had been supplied with a trailing "#").  But if the linked input to
    ' importSpill was broken (and the Spill replaced with a range of values), LS will
    ' find the BRC of the data from the Note importSpill affixed to the TLC.
        
    If TLC.HasSpill Then
        LS = TLC.SpillingToRange
    Else
        Dim crumbs As Variant
        crumbs = Split(TLC.Comment.Text, "x")
        
        Dim BRC As Range
        Set BRC = TLC.Worksheet.Cells( _
            TLC.Row + crumbs(0) - 1, _
            TLC.Column + crumbs(1) - 1 _
        )
        
        LS = Range(TLC, BRC)
    End If
    
    Exit Function
    
BAIL_OUT:

    LS = CVErr(xlErrNA)
End Function

We can now simply write

=VLOOKUP(someValue,LS(AA100),2,FALSE)

which is rather satisfyingly concise.  Similarly, if we wanted to index that Spill,

=INDEX(LS(AA100),1,4)

will do that.

The processing overheads in the production sheet (with the live links) are miniscule since the UDF instantly returns the available Spill.  The overheads of processing the breadcrumbs inside the Note only fall to the frozen copy of the Workbook, which seems acceptable to me.

PS:  Contrasting the response of ChatGBT to @NikolinoDE to the evolution of my (human) thinking across this thread is fascinating.  A truly intelligent response to my OP would have read "Don't be so blooming stupid.  Consider that instead of writing INDEXLS, you could just write LS far more easily and plug it into the standard INDEX function."