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

0 votes

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:

enter image description here

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.

enter image description here

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?

Mar 23, 2023 in Others by Kithuzzz
• 38,010 points
772 views

1 answer to this question.

0 votes

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
answered Mar 23, 2023 by narikkadan
• 63,700 points

Related Questions In Others

0 votes
1 answer

How to increment the Range of a For Each loop - Excel VBA

Your formula seems to sum 1 single ...READ MORE

answered Jan 7, 2023 in Others by narikkadan
• 63,700 points
2,200 views
0 votes
1 answer

How to automatically assign a color to the maximum and minimum values in a set of selected cells in Excel?

See Conditional Formatting, which may be accessed ...READ MORE

answered Apr 7, 2023 in Others by Kithuzzz
• 38,010 points
335 views
0 votes
1 answer

Excel-VBA - How to identify Target range (more than 1 cell) is deleted in a Worksheet_Change function?

You misunderstand the purpose of the function ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,700 points
3,201 views
0 votes
1 answer

How to get rid of a #value error in Excel?

Changing the format to "Number" doesn't actually ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,700 points
495 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
947 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,276 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
570 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
801 views
0 votes
1 answer

How to automatically get a specified range of data from Excel to XML in VBA

Range method works, always identify the sheet ...READ MORE

answered Mar 18, 2023 in Others by narikkadan
• 63,700 points
571 views
0 votes
1 answer

How to add Conditional Formatting in Excel for a Range of Values

Three distinct rules are required, one for ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 63,700 points
839 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP