Aug 13 2023 03:11 AM - edited Aug 13 2023 04:03 AM
Aug 13 2023 03:11 AM - edited Aug 13 2023 04:03 AM
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.
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
I could now write
Thoughts / feedback welcome.
Aug 14 2023 05:42 AM
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:
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:
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:
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.
Aug 14 2023 07:10 AM - edited Aug 14 2023 07:17 AM
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.
Aug 14 2023 12:23 PM - edited Aug 15 2023 02:37 AM
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
which is rather satisfyingly concise. Similarly, if we wanted to index that Spill,
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."