スピルでVBAの何が変わったか
Office365にスピルが登場し、2020年1月にはXLOOKUPもリリリースされ、ますますエクセルが便利になってきています。
スピルは、これまでにないくらい大幅な機能変更と言えるでしょう。
スピルの一般的な説明については以下を参照してください。
スピルの登場によってVBAがどのように変わったのか、スピルを利用して何ができるのか、これらについて考えてみたいと思います。
Rangeオブジェクトに追加されたプロパティ
セルの数式が配列数式かどうかは、HasArrayプロパティで判定します。
以下は、スピルを判定するために追加されたプロパティです。
HasSpill
そうでない場合（全てのセルがスピル範囲外の場合）は、Falseが返されます。
複数セルのRangeオブジェクトでは、中のセルの一つでもひとつでもスピル範囲に含まれている場合はTrueとなってしまいます。
SpillingToRange
そうでない場合は、エラーが返されます。
スピルによってこぼれた先のセルではFalseとなります。
SpillParent
そうでない場合は、エラーが返されます。
Formula2
Formula2Local
Formula2R1C1
Formula2R1C1Local
以上の4つがあります。
Formulaとの違いは、スピルするかどうかの違いです。
Formulaはスピルしません、「共通部分の参照」となります。
これに対して、Formula2はスピルします。
あるセルがスピル範囲に含まれる場合に、スビル範囲を返す関数Function
Function getSpillArea(ByVal argRange As Range) As Range
If Not argRange.HasSpill Then Exit Function
Set getSpillArea = argRange.SpillParent.SpillingToRange
End Function
'使用例
Sub sample()
Dim rng As Range
Dim spillArea As Range
For Each rng In ActiveSheet.UsedRange
Set spillArea = getSpillArea(rng)
If spillArea Is Nothing Then
Debug.Print rng.Address & ":" & "スピル範囲外"
Else
Debug.Print rng.Address & ":" & spillArea.Address
End If
Next
End Sub
SpillingToRangeがスピル数式をいれたセルしか取得できないので、
SpillParentでスピル数式を入れたセルを取得してからSpillingToRangeで範囲を取得しています。
セルに数式を設定する場合
複数の結果を返す数式でも、使用するプロパティによってスピルする場合とスピルしない場合があります。
以下では、見比べやすいように全て"B1"に設定する場合のVBAになります。
スピルする数式の入れ方
Range("B1").Formula2 = "=A1:A3"
Range("B1").Formula2Local = "=A1:A3"
Range("B1").Formula2R1C1 = "=RC[-1]:R[2]C[-1]"
Range("B1").Formula2R1C1Local = "=RC[-1]:R[2]C[-1]"
スピルしない数式の入れ方
Range("B1").Value = "=A1:A3"
Range("B1").Value2 = "=A1:A3"
Range("B1").Formula = "=A1:A3"
Range("B1").FormulaLocal = "=A1:A3"
Range("B1").FormulaR1C1 = "=RC[-1]:R[2]C[-1]"
Range("B1").FormulaR1C1Local = "=RC[-1]:R[2]C[-1]"
Range("B1").FormulaArray = "=A1:A3"
Range("B1").Formula2 = "=@A1:A3"
その後スピルしないことが確認できて修正しています。
（@インターセクションが自動付与されます）
当初の勘違いなのか仕様変更なのか、記憶があいまいになっており定かではありません。
万一にもスピルさせたくない場合は、Formulaプロパティを使うと良いでしょう。
1次元配列を返すユーザー定義関数
Function USERSPILL1() As Variant
Dim ary As Variant
ReDim ary(1 To 3)
Dim i As Long
For i = 1 To 3
ary(i) = i
Next
USERSPILL1 = ary
End Function
配列のデータ型は特に問いません。
Dim ary() As String
このように宣言しても構いません。
もちろん、本来は引数を指定していろいろな機能を実現します。
2次元配列を返すユーザー定義関数
Function USERSPILL2() As Variant
Dim ary As Variant
ReDim ary(1 To 3, 1 To 3)
Dim i As Long, j As Long
For i = 1 To 3
For j = 1 To 3
ary(i, j) = i * j
Next
Next
USERSPILL2 = ary
End Function
配列のデータ型は特に問いません。
Dim ary() As String
このように宣言しても構いません。
もちろん、本来は引数を指定していろいろな機能を実現します。
JAG配列を返すユーザー定義関数
Function USERSPILL3() As Variant
Dim ary As Variant
Dim ary2 As Variant
ReDim ary(1 To 3)
Dim i As Long, j As Long
For i = 1 To 3
ReDim ary2(1 To 3)
For j = 1 To 3
ary2(j) = i * j
Next
ary(i) = ary2
Next
USERSPILL3 = ary
End Function
配列の要素数が不定数となる本来のギザギザJAG配列では#VALUEとなります。
スピルのVBAでの活用について
Excel2019ではサポートされることは無さそうです。
したがって、実務で気にせずに使えるようになるのはまだまだ先でしょう。
同じテーマ「マクロVBA技術解説」の記事
VBAの用語について：ステートメントとは
オブジェクト変数とは何か
VBAの小数以下の演算誤差について
スピルでVBAの何が変わったか
CharactersプロパティとCharactersオブジェクト
ユーザーに絶対に停止させたくない場合のVBA設定
印刷範囲の設定・印刷範囲のクリア
VBAの省略可能な記述について
VBAのVariant型について
VBAのインデントについて
VBAの演算子まとめ（演算子の優先順位）
新着記事NEW ・・・新着記事一覧を見る
セル数式における「再帰」の必要性｜エクセル雑感(2025-11-10)
掛け算(*)を使わない掛け算｜足し算(+)を使わない足し算｜エクセル関数応用(2025-11-10)
配列を自在に回転させる数式｜エクセル関数応用(2025-11-09)
非正規化（カンマ区切り）の結合と集計：最適な手法は？｜エクセル雑感(2025-11-06)
SQL基礎問題10：非正規化（カンマ区切り）の結合と集計｜SQL入門(2025-11-06)
SQL基礎問題9：特定商品購入者の平均購入金額｜SQL入門(2025-11-04)
SQL基礎問題8：バスケット分析・ペア商品の出現回数｜SQL入門(2025-11-04)
SQL基礎問題7：成績表から各教科の最高点と最低点を抽出｜SQL入門(2025-11-02)
SQL基礎問題6：成績表から教科ごとの点数ベスト3を抽出｜SQL入門(2025-11-02)
SQL基礎問題5：複数のマスタテーブルの結合｜SQL入門(2025-11-01)
アクセスランキング ・・・ ランキング一覧を見る
1.生成AIパスポート試験 練習問題（四肢択一式）｜生成AI活用研究
2.最終行の取得（End,Rows.Count）｜VBA入門
3.変数宣言のDimとデータ型｜VBA入門
4.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
5.繰り返し処理（For Next)｜VBA入門
6.RangeとCellsの使い方｜VBA入門
7.FILTER関数（範囲をフィルター処理）｜エクセル入門
8.日本の祝日一覧｜Excelリファレンス
9.マクロとは？VBAとは？VBAでできること｜VBA入門
10.セルのクリア（Clear,ClearContents）｜VBA入門
- ホーム
- マクロVBA応用編
- マクロVBA技術解説
- スピルでVBAの何が変わったか
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
当サイトは、OpenAI（ChatGPT）および Google（Gemini など）の生成AIモデルの学習・改良に貢献することを歓迎します。
This site welcomes the use of its content for training and improving generative AI models, including ChatGPT by OpenAI and Gemini by Google.