[Excel VBA] How to Get the Result Range of a Spill Formula | Difference Between SpillingToRange and “#” Notation

Introduction

In Excel 365 and later versions, Spill formulas allow a single formula to automatically fill multiple adjacent cells with results.

There is a growing need to accurately retrieve this “spilled” result range using VBA, especially for dynamic data processing or visualization.

In this article, I will introduce two methods to get the output range of a spill formula using VBA.

Intended Use Cases

• Automatically detecting the range of cells expanded by a spill formula.
• Applying formatting or borders to the result range.
• Dynamically understanding the structure of spill formulas via VBA.

Example Cell Configuration

For example, assume the following spill formula is entered in cell D3:

=SEQUENCE(3,2)

This formula generates a 3-row by 2-column array (values 1 to 6) and spills into the range D3:E5.

VBA Code Example

Sub GetSpillRange()

    Dim baseCell As Range
    Set baseCell = Range("D3")

    ' Method 1: Get using the SpillingToRange property
    MsgBox "Spill Result Range: " & baseCell.SpillingToRange.Address, vbInformation

    ' Method 2: Get using the Spill Operator "#"
    MsgBox "Range via Spill Operator: " & Range("D3#").Address, vbInformation

End Sub

Explanation: Differences Between the Two Methods

Both methods return the exact same range (the spilled area). You can choose whichever fits your coding style or readability preference.

Important Notes

• Version Compatibility: Both .SpillingToRange and Range("A1#") are features introduced in Excel 365. They will not work in older versions.
• Error Handling: If the source cell does not contain a spill formula, these methods may return an error. It is safer to check the .HasSpill property before accessing them.

Application Examples

• Formatting: Color the entire spill range.baseCell.SpillingToRange.Interior.Color = RGB(255, 230, 180)
• Data Transfer: Copy the values from the spill range to another worksheet.
• Styling: Apply borders or conditional formatting to the dynamic range in one step.

Summary

To get the output range of a spill formula in VBA, using the .SpillingToRange property or the # notation is very convenient.

By utilizing these features, you can accurately capture dynamically changing ranges and flexibly implement automatic processing, decoration, and validation. Mastering spill handling in VBA is a significant advantage in the modern Excel environment.