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?

Mar 23 in Others 114 views

## 1 answer to this question.

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```
• 63,040 points

## 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

## 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

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

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

## Retrieve epay.info Balance with VBA and Excel

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

## How to load file to Excel Power query from SFTP site

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

## Using VBA Excel to create a gramatically correct list

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