VBA技術解説
スピルでVBAの何が変わったか

ExcelマクロVBAの問題点と解決策、VBAの技術的解説
公開日：2020-02-03　最終更新日：2021-08-27

スピルでVBAの何が変わったか


Office365にスピルが登場し、2020年1月にはXLOOKUPもリリリースされ、ますますエクセルが便利になってきています。
スピルは、これまでにないくらい大幅な機能変更と言えるでしょう。
スピルの一般的な説明については以下を参照してください。

スピルについて
・スピルとは ・スピルの数式例 ・ゴースト ・スピル範囲での独特な挙動について ・スピルのエラー表示 ・スピル範囲演算子 ・暗黙的なインターセクション演算子 ・従来のスピルしないエクセルとの互換性についての注意点 ・スピル関連記事


スピルの登場によってVBAがどのように変わったのか、スピルを利用して何ができるのか、これらについて考えてみたいと思います。



Rangeオブジェクトに追加されたプロパティ

セルに数式が設定されているかどうかは、HasFormulaプロパティで判定します。
セルの数式が配列数式かどうかは、HasArrayプロパティで判定します。
以下は、スピルを判定するために追加されたプロパティです。

HasSpill

範囲内のいずれかのセルがスピル範囲に含まれる場合は、Trueが返されます。
そうでない場合（全てのセルがスピル範囲外の場合）は、Falseが返されます。

単一セルのRangeオブジェクトで使用するようにしてください。
複数セルのRangeオブジェクトでは、中のセルの一つでもひとつでもスピル範囲に含まれている場合はTrueとなってしまいます。

Areasが複数存在するRangeオブジェクトの場合はエラーとなります。

SpillingToRange

セルの数式がスピルする場合は、スピル範囲を返します。
そうでない場合は、エラーが返されます。

つまり、スピルする数式を入れたセルでのみTrueが返ります。
スピルによってこぼれた先のセルではFalseとなります。

SpillParent

セルがスピルのメンバーである場合は、スピルする数式を入れたセルを返します。
そうでない場合は、エラーが返されます。

Formula2

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で範囲を取得しています。


セルに数式を設定する場合

VBAで配列を返すような数式をセルに設定することはあまり無いとは思いますが、意図せずそうなってしまう事はあるかもしれません。
複数の結果を返す数式でも、使用するプロパティによってスピルする場合とスピルしない場合があります。
以下では、見比べやすいように全て"B1"に設定する場合のVBAになります。

スピルする数式の入れ方

Formula2を使う事でスピルします。
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]"

スピルしない数式の入れ方

Formulaはスピルしません。
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"
このページでは、当初Value,Value2でもスピルするように書いていました。
その後スピルしないことが確認できて修正しています。
（@インターセクションが自動付与されます）
当初の勘違いなのか仕様変更なのか、記憶があいまいになっており定かではありません。

VBAで設定した後には必ず実際のセルを確認するはずですので、スピルしていればその時点で気が付くとは思います。
万一にもスピルさせたくない場合は、Formulaプロパティを使うと良いでしょう。


1次元配列を返すユーザー定義関数

1次元配列を返す関数Functionをシートでユーザー定義関数として使用すればスピルします。

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
このように宣言しても構いません。
もちろん、本来は引数を指定していろいろな機能を実現します。

VBA マクロ スピル spill

※旧（スピルしない）エクセルでは、配列数式{=USERSPILL1()}となります。

VBA マクロ スピル spill


2次元配列を返すユーザー定義関数

2次元配列を返す関数Functionをシートでユーザー定義関数として使用すればスピルします。

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
このように宣言しても構いません。
もちろん、本来は引数を指定していろいろな機能を実現します。

VBA マクロ スピル spill

※旧（スピルしない）エクセルでは、配列数式{=USERSPILL2()}となります。


JAG配列を返すユーザー定義関数

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 マクロ スピル spill

※旧（スピルしない）エクセルでは、配列数式{=USERSPILL3()}となります。


スピルのVBAでの活用について

スピルは、永続版のExcelでサポートされるのは次期バージョンからになりそうです。
Excel2019ではサポートされることは無さそうです。
したがって、実務で気にせずに使えるようになるのはまだまだ先でしょう。

とはいえ、Office365も大分普及しているようですので、Excelファイルを共有する範囲内のPCが全てOffice365になっているのであればスピルや新関数を使う事に問題はありません。

使い方を工夫すれば、強力な武器になると思います。



同じテーマ「マクロVBA技術解説」の記事

VBAの用語について：ステートメントとは
オブジェクト変数とは何か
VBAの小数以下の演算誤差について
スピルでVBAの何が変わったか
CharactersプロパティとCharactersオブジェクト
ユーザーに絶対に停止させたくない場合のVBA設定
印刷範囲の設定・印刷範囲のクリア
VBAの省略可能な記述について
VBAのVariant型について
VBAのインデントについて
VBAの演算子まとめ（演算子の優先順位）


新着記事NEW ・・・新着記事一覧を見る

TRIMRANGE関数（セル範囲をトリム：端の空白セルを除外）｜エクセル入門(2024-08-30)
正規表現関数（REGEXTEST,REGEXREPLACE,REGEXEXTRACT）｜エクセル入門(2024-07-02)
エクセルが起動しない、Excelが立ち上がらない｜エクセル雑感(2024-04-11)
ブール型（Boolean）のis変数・フラグについて｜VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する｜VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次｜エクセルの神髄(2024-03-20)
VBA10大躓きポイント（初心者が躓きやすいポイント）｜VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成｜ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。｜エクセル雑感(2024-02-17)
初級脱出10問パック｜VBA練習問題(2024-01-24)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.繰り返し処理（For Next)｜VBA入門
3.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
4.変数宣言のDimとデータ型｜VBA入門
5.RangeとCellsの使い方｜VBA入門
6.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
7.セルのクリア（Clear,ClearContents）｜VBA入門
8.メッセージボックス(MsgBox関数)｜VBA入門
9.条件分岐（Select Case）｜VBA入門
10.ブック・シートの選択（Select,Activate）｜VBA入門



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ
同じテーマの記事
新着記事
アクセスランク


↑