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用語の最後に
オブジェクト変数とは何か
・そもそも変数とは何か ・変数の入れ物としての大きさは ・オブジェクト変数にオブジェクトは入っていない ・オブジェクト変数のメモリアドレス ・Is演算子によるオブジェクトの比較 ・TypeOf演算子 ・オブジェクト変数を使う時の注意点 ・オブジェクト変数の最後に
VBAの小数以下の演算誤差について
・小数以下の演算誤差とは ・小数以下の演算誤差を実際のVBAで確認 ・データ型で小数以下の演算誤差に対応 ・小数以下の演算誤差にデータ型以外で対応する ・VBAの小数以下の演算誤差の最後に
スピルでVBAの何が変わったか
CharactersプロパティとCharactersオブジェクト
・Charactersプロパティ ・Charactersオブジェクト ・CharactersプロパティとCharactersオブジェクトの使用例
ユーザーに絶対に停止させたくない場合のVBA設定
ユーザー操作によりマクロVBAが実行中に停止させられてしまう場合があります。再実行可能であれば問題ありませんが、途中で強制停止された場合のデータ保証が出来ないと言った場合もあるでしよう。マクロ実行でも、以下のキーボード操作でVBAは停止できてしまいます。
印刷範囲の設定・印刷範囲のクリア
・印刷範囲の設定 ・印刷範囲の取得 ・印刷範囲のクリア
VBAの省略可能な記述について
・変数の宣言 ・変数宣言のデータ型 ・Application.ScreenUpdating = True : Application.DisplayAlerts = True ・Applicationのプロパティ ・Applicationのメソッド ・Range.Value ・プロシージャーのCall ・メソッドのCall ・Let ・Next 変数 ・Step 1 ・引数が無い場合の() ・名前付き引数 ・メソッドの規定値 ・組み込み列挙 ・関数のモジュール名 ・コレクションのItem ・VBAの省略可能な記述の最後に
VBAのVariant型について
・Variant型変数のデータ型を調べる ・Variant型変数のメモリアドレス ・Variant型どうしの算術演算 ・Variant型どうしの比較演算 ・RangeのValueプロパティの比較 ・ユーザー定義型(Type)とバリアント(Variant)変数 ・Variant型の配列 ・配列をVariant型の仮引数に渡す ・RangeオブジェクトをVariant型の仮引数に渡す
VBAのインデントについて
・VBAのインデントの模範 ・VBAのブロックについて ・VBAのインデントの具体例 ・インデントの最後に
VBAの演算子まとめ(演算子の優先順位)
・演算子の優先順位 ・算術演算子 ・比較演算子 ・論理演算子 ・連結演算子 ・単項演算子


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

テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)
スピらない スピル数式 スピらせる|エクセル雑感(2023-12-06)
イータ縮小ラムダ(eta reduced lambda)|エクセル入門(2023-11-20)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門




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


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



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