How to programmatically get the values of a spilled Excel range in VBA?

Question

Range value properties all return EMPTY in executing code.

How can I programmatically get the values of a spilled range?

I'm using the following code as a test function, you can copy and paste into some module:

Public Function TestFunction(n As Integer, bIsVertical As Boolean) As Variant

Dim i               As Integer
Dim vals            As Variant
Dim rng             As Excel.Range
Dim rngEnabled      As Excel.Range
Dim bShortCircuit   As Boolean

    On Error Resume Next
    Set rng = ActiveWorkbook.Names("SHORT_CIRCUIT").RefersToRange
    If Not rng Is Nothing Then
        bShortCircuit = CBool(rng.value)
    End If
    On Error GoTo 0
    
    If bShortCircuit Then
        Set rng = Application.caller

        If Not rng.SpillParent Is Nothing Then
            n = rng.SpillParent.SpillingToRange.Cells.count
        End If
        
        If bIsVertical Then
            ReDim vals(0 To n - 1, 0)
            For i = 0 To n - 1
                Debug.Print i & " -- ", rng.Offset(i).value
                vals(i, 0) = rng.Offset(i).value
            Next i
        Else
            ReDim vals(0 To n - 1)
            For i = 0 To n - 1
                Debug.Print i & " -- ", rng.Offset(0, i).value
                vals(i) = rng.Offset(0, i).value
            Next i
        End If
        TestFunction = vals
        Exit Function
    End If

    If bIsVertical Then
        ReDim vals(0 To n - 1, 0)
        For i = 0 To n - 1
            vals(i, 0) = i
        Next i
    Else
        ReDim vals(0 To n - 1)
        For i = 0 To n - 1
            vals(i) = i
        Next i
    End If
    TestFunction = vals
    
End Function

Based on n and bIsVertical it will print a dynamic range result to Excel if "SHORT_CIRCUIT" range does not exist or it's value is FALSE. If "SHORT_CIRCUIT" is TRUE, however, the existing function values should be returned as the result.

What I am trying to do, is add an enable/disable feature to an add-in (long story short, turning calculation mode to manual does not work in all cases). I thought this would be relatively easy, but Excel gives me the following error message as soon as I change an input to the function (n or bIsVertical) when short-circuiting is TRUE:

Furthermore, when I investigated what is being written to vals during this event, I see that the elements of the returned array are all EMPTY, which appears to be the root of the problem because if I manually define vals and return a non-empty result then I do not get the error pop-up.

I've tried to get the caller values from Value and Value2 range properties, and also SpillingParent.SpillingToRange but those do not work. The vexing thing is that I can see the values in the Properties window when debugging or Debug.Print directly in the Immediate window (not as a statement in the executing code, which also returns EMPTY).

So, my question, is how can I programmatically get the values of a spilled range?

Instead of using a 1D array and a loop, why not just read the `.Value` of the entire spilled range into a 2D array? — BigBen, Mar 22 '23 at 16:53
I tried that, but the value properties all return empty when the code executes — ChE Junkie, Mar 22 '23 at 16:56
Because I need to get the range that the formula is currently in. Then if short circuit is active, I try to return the existing values as the result (from the last time the function was allowed to calculate naturally). — ChE Junkie, Mar 22 '23 at 17:01
Getting the range that the formula is in, and then getting the splllparent from that, sounds like a circular reference. — BigBen, Mar 22 '23 at 17:02
Which is why I am attempting to return the values from the SpillParent, not the SpilledParent object itself. If I return a non-empty variant as the result, then I do not get the pop-up. The problem is when I attempt to programmatically read the values of the spilled range into a variant type to return, which then returns empty when they are not. — ChE Junkie, Mar 22 '23 at 17:09
Unclear exactly what you're doing, but you could maybe try caching `vals` in a Static variable inside the function, and just return that if short-circuiting? It would mean the function would need to run at least once before that process could take over... — Tim Williams, Mar 22 '23 at 17:10
Putting Excel into manual calculation mode does not work in all cases. For example, if you open a workbook that was saved in manual calculation mode into an instance of Excel that is already setup to calculate automatically, then the functions will recalculate. The state of the workbook is not respected because it is an application level switch. So, I wanted to prevent functions from updating if the workbook name "SHORT_CIRCUIT" exists and is True. When short-circuiting all functions should return their existing results (last calculated values). But I get all empty elements when I try. — ChE Junkie, Mar 22 '23 at 17:15
Caching will not work, because there would be multiple calls to the same function with different inputs. — ChE Junkie, Mar 22 '23 at 17:15
Think `CBool(rng.Value)` function applied upon an entire *datafield* won't result in a valid boolean value, so `bShortCircuit` won't never be assigned to `True` as precondition to get vals from your spilled range. Btw if you reference only the top target cell in the spilling range by the `#` suffix - e.g. Sheet1.Range("D2#")` - you have already all needed data, either by naming it `SHORT_CIRCUIT` or by direct reference or by assignment to a datafield array. @ChEJunkie — T.M., Mar 22 '23 at 19:45

score 0 · Accepted Answer · answered Mar 22 '23 at 18:59

I was able to bypass the issue by using the Text property. That is the only property for a spilled range that returns its value. I then cast it as a double if it IsNumeric or as a string otherwise.

For those who are interested, here is the modified TestFunction that illustrates the idea described the question details.

Public Function TestFunction(n As Integer, bIsVertical As Boolean) As Variant

Dim i               As Integer
Dim vals            As Variant
Dim rng             As Excel.Range
Dim rngEnabled      As Excel.Range
Dim bShortCircuit   As Boolean
Dim cell            As Excel.Range
Dim nRows           As Long
Dim nCols           As Long

    On Error Resume Next
    Set rng = ActiveWorkbook.Names("SHORT_CIRCUIT").RefersToRange
    If Not rng Is Nothing Then
        bShortCircuit = CBool(rng.value)
    End If
    On Error GoTo 0

    If bShortCircuit Then
        Set rng = Application.Caller
        If rng.SpillParent Is Nothing Then
            Exit Function
        End If
        
        n = rng.SpillingToRange.Cells.count
        nRows = rng.SpillingToRange.Rows.count
        nCols = rng.SpillingToRange.Columns.count
        
        If nRows = 1 And nRows = nCols Then
            vals = rng.SpillingToRange.Text
        ElseIf nRows = 1 And nCols > 1 Then ' horizontal
            ReDim vals(1 To nCols)
            For i = 1 To nCols
                vals(i) = rng.SpillingToRange.Cells(i).Text
            Next i
        Else ' vertical
            ReDim vals(1 To nRows, 0)
            For i = 1 To nRows
                If IsNumeric(rng.SpillingToRange.Cells(i).Text) Then
                    vals(i, 0) = CDbl(rng.SpillingToRange.Cells(i).Text)
                Else
                    vals(i, 0) = rng.SpillingToRange.Cells(i).Text
                End If
            Next i
        End If
        
        TestFunction = vals
        Exit Function
    End If

    If bIsVertical Then
        ReDim vals(0 To n - 1, 0)
        For i = 0 To n - 1
            vals(i, 0) = i
        Next i
    Else
        ReDim vals(0 To n - 1)
        For i = 0 To n - 1
            vals(i) = i
        Next i
    End If
    TestFunction = vals
    
End Function

Btw, `Text` won't return the full value, only what is displayed in the cell. This will fail for example if the cell contains a number but the cell width is too narrow, causing (multiple) `#` to be displayed. — BigBen, Mar 22 '23 at 19:20

How to programmatically get the values of a spilled Excel range in VBA?

1 Answers1