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

2025年12月27日

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

Method	Description	Availability
`.SpillingToRange`	Directly retrieves the range object where the formula spills using a VBA property.	Excel 365 or later
`Range("Cell#")`	Refers to the result range using the spill operator (`#`) syntax inside `Range()`.	Excel 365 or later

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.

よかったらシェアしてね！