INDEXLS to index Linked Spills in foreign Workbooks

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.