スピルで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 ・・・新着記事一覧を見る
ブール型（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)
累計を求める数式あれこれ｜エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE｜エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。｜エクセル雑感(2023-12-28)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
3.RangeとCellsの使い方｜VBA入門
4.ひらがな⇔カタカナの変換｜エクセル基本操作
5.繰り返し処理（For Next)｜VBA入門
6.変数宣言のDimとデータ型｜VBA入門
7.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
8.並べ替え（Sort）｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.Findメソッド（Find,FindNext,FindPrevious）｜VBA入門
- ホーム
- マクロVBA応用編
- マクロVBA技術解説
- スピルでVBAの何が変わったか
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。