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?

Answer 1

By using the Text property, I was able to get around the problem. With a spilled range, only that property returns its value. Then, depending on whether it is numeric or not, I cast it as a double or a string.

Here is a modified TestFunction that exemplifies the concept discussed in the question's specifics for anyone who are interested.

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