Excel 365以降で導入されたスピル（Spill）数式では、1つのセルに入力された数式が、複数のセルに自動的に展開されます。
この展開された結果セル範囲を、VBAを使って正確に取得したいというニーズは多く、特に動的なデータ処理や構造の可視化時に役立ちます。
本記事では、VBAを用いてスピル数式の出力先範囲を取得する方法を2通りご紹介いたします。
目次
想定される用途
- スピル数式の結果として展開されたセル範囲を自動で検出したい。
- 結果範囲に書式や枠線を適用したい。
- スピル数式の構造をVBAで動的に把握したい。
使用するセル構成の例
たとえばセル D3 に以下のようなスピル数式が入力されているとします：
=SEQUENCE(3,2)
この数式は、3行2列の配列（1～6）を生成し、セル D3:E5 に展開されます。
VBAコードの例
Sub GetSpillRange()
Dim baseCell As Range
Set baseCell = Range("D3")
' SpillingToRangeプロパティで取得
MsgBox "Spill結果の範囲：" & baseCell.SpillingToRange.Address, vbInformation
' 「#」記号を使った参照で取得
MsgBox "スピル記号参照での範囲：" & Range("D3#").Address, vbInformation
End Sub
解説：2つの方法の違い
| 方法 | 内容 | 利用可否 |
|---|---|---|
.SpillingToRange | スピル結果が展開されているセル範囲をVBAから直接取得する方法です。 | Excel 365以上のみ |
Range("セル#") | スピル記号（#）を用いた構文で結果範囲を参照する方法です。 | Excel 365以上のみ |
どちらも同じ範囲（スピル結果のセル範囲）を返しますが、構文としての用途や可読性の観点から使い分けが可能です。
注意点
.SpillingToRangeやRange("A1#")はExcel 365以降の機能です。旧バージョンでは使用できません。- スピル元セルに数式が存在しない場合、これらの構文はエラーを返します。
HasSpillで事前チェックすると安全です。
応用例
baseCell.SpillingToRange.Interior.Color = RGB(255, 230, 180)のように、スピル範囲全体に色を付ける。- スピル範囲の値を別のワークシートにコピー。
- スピル範囲に枠線・条件付き書式を一括適用。
まとめ
スピル数式の出力範囲をVBAで取得するには、.SpillingToRangeプロパティまたは#記法による範囲参照が非常に便利です。
これらを活用すれば、動的に変化するスピル範囲を正確に捉え、自動処理や装飾、チェック作業を柔軟に実装することができます。
動的配列がますます使われる現代のExcel環境において、スピルの取り扱いをVBAでもマスターしておくことは、大きな強みとなるでしょう。
