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

ExcelマクロVBAの問題点と解決策、VBAの技術的解説
最終更新日:2020-02-03

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


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

スピルについて
2019年にOffice365のExcelに実装された革新的な機能としてスピルがあります。数式を入力したセルから結果があふれて隣接したセルにも出力されるのがスピルです。今までは数式を入れたセルにしか結果を出せませんでしたが、スピルでは隣接するセルにまで結果が表示されます。


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

スピルする数式の入れ方

Value、Value2、Formula2はスピルします。
Range("A1").Value = "=A1:A3"
Range("A1").Value2 = "=A1:A3"
Range("A1").Formula2 = "=A1:A3"
Range("A1").Formula2Local = "=A1:A3"
Range("A1").Formula2R1C1 = "=RC[-1]:R[2]C[-1]"
Range("A1").Formula2R1C1Local = "=RC[-1]:R[2]C[-1]"

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

Formulaはスピルしません。
Range("A1").Formula = "=A1:A3"
Range("A1").FormulaLocal = "=A1:A3"
Range("A1").FormulaR1C1 = "=RC[-1]:R[2]C[-1]"
Range("A1").FormulaR1C1Local = "=RC[-1]:R[2]C[-1]"
Range("A1").FormulaArray = "=A1:A3"

当然ではありますが、暗黙的なインターセクション演算子@を付ければスピルしません。
Range("M1").Value = "=@A1:A3"

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技術解説」の記事

Colorプロパティの設定値一覧
VBAを定型文で覚えよう
VBAこれだけは覚えておきたい必須基本例文10
エクセルVBAでのシート指定方法
文字列結合&でコンパイルエラーになる理由
手動計算時の注意点と再計算方法
VBAの用語について:ステートメントとは
オブジェクト変数とは何か
VBAの少数以下の演算誤差について
スピルでVBAの何が変わったか
CharactersプロパティとCharactersオブジェクト


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

ユーザーに絶対に停止させたくない場合のVBA設定|VBA技術解説(4月1日)
CharactersプロパティとCharactersオブジェクト|VBA技術解説(3月31日)
指数近似/対数近似/累乗近似(掲載順位とCTR)|エクセル関数超技(3月31日)
練習問題32(連続数値部分を取り出し記号で連結)|VBA練習問題(3月24日)
連続数値部分を取り出し記号で連結|エクセル関数超技(3月24日)
数式バーの高さを数式の行数で自動設定|VBAサンプル集(3月21日)
LET関数(数式で変数を使う)|エクセル入門(3月21日)
スピルに対応したXSPLITユーザー定義関数(文字区切り)|VBAサンプル集(3月15日)
XMATCH関数(範囲から値を検索し一致する相対位置)|エクセル入門(3月14日)
XLOOKUP関数(範囲を検索し一致する対応項目を返す)|エクセル入門(3月14日)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.マクロって何?VBAって何?|VBA入門
5.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
6.変数宣言のDimとデータ型|VBA入門
7.ひらがな⇔カタカナの変換|エクセル基本操作
8.繰り返し処理(For Next)|VBA入門
9.徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)|エクセル関数超技
10.セルに文字を入れるとは(Range,Value)|VBA入門




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


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



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