Excel VBA 動的配列には Range.Formula2

Excel VBA 動的配列には Range.Formula2

前回記事で、VBAでXLOOKUP関数を使うテストをした際に、数式をセルに書き込むテストもやってみたところ Formula ではダメなことに今更ながら気づかされました。

Excelがスピル機能を使えるようになってから、VBAでセルに数式を書き込むことが無かったので気づかずにいましたが、勉強不足でした。

Excel VBA でセルに動的配列関数や数式を入れたり取得したりする方法を解説します。

スピルする動的配列数式が使えるのは、サブスクリプション版の「Microsoft 365」アプリの「Excel for Microsoft 365」と永続ライセンス版の最新版 Excel 2021です。

くるみこ
くるみこ

VBAでセルに数式を書き込む際には今までは Range.Formulaプロパティを使っていましたが、動的配列が有効なExcelでは Range.Formula2プロパティが新しく用意されていました。
Range.Formulaは互換性を維持するために残されたプロパティになったようです。

数式をセルに書き込む方法については、以前も勉強しましたよね。復習も兼ねて勉強したいと思います。よろしくお願いしますm(__)m

【この記事でわかることは】
・Excel VBA Range.Formula2 でセルに動的配列数式を書き込む方法
・Excel のビルド番号でスピル機能の有無を判断して処理を分岐する方法

スポンサーリンク

VBAでセルに関数や数式を入力する

Range.Formula を使う記事についてはこちらも参照してください。

「A1」に関数や数式を埋め込む記述は、Range(“A1”).Formula プロパティにセルに入力するのと同じ数式を文字列で設定します。 【 記述例: Range(“A1”).Formula = “=SUM(A2:A10)” 】

Formula2 と Formula の違い

また前回記事の例(XLOOKUP 関数)を使って Formula2 と Formula の違いをみましょう。

XLOOKUP関数の完全一致検索スピル使用例

まずは Formula でセットしてみます

'XLOOKUPの数式をセルに Formula で入力する
Sub Formula_test01()
    Range("H2").Formula = "=XLOOKUP(G2,A2:A101,B2:E101,""該当なし"")"
End Sub

【結果画像】

FomulaプロパティではXLOOKUPの数式はエラーになる
数式選択時に表示された「@演算子」の説明

数式の先頭に「@演算子」が挿入されているため #VALUE エラーになってしまいました。
Formula で動的配列数式(スピル)の関数を入力すると、関数に「@演算子」が付いてスピルしない数式になります。「@演算子」は関数だけでなく動的配列数式のセル範囲にも付加されます。

Formula2 を使ってみます

'XLOOKUPの数式をセルに Formula2 で入力する
Sub Formula_test02()
    Range("H2").Formula2 = "=XLOOKUP(G2,A2:A101,B2:E101,""該当なし"")"
End Sub

【結果画像】

XLOOKUPの数式をFormula2でセルに書き込む

Formula2 では問題なく指定通りに数式が書き込まれ、計算結果をスピルしました。

Range.Formula2 プロパティは従来のExcelで使えるのか

結論から言うと、スピル対応していない旧バージョンのExcelでは Formula2 プロパティは使えません。Formula2 プロパティはスピル対応のために用意されたバージョンということです。

実際に、旧バージョンの Excel2016 で使った場合どうなるのか試してみたところ、「このプロパティをサポートしていない」旨のエラーメッセージが表示され機能しませんでした。

Excel ビルド番号をチェックして使い分ける方法

一般的なバージョンチェックする方法ではExcel2016以降は全て「1.6」と返ってくるのでビルド番号でチェックして分岐することにしました。

スピル機能が最初に登場したのは、2019年11月更新(バージョン 1911、ビルド 12228.20332)のようです。このビルド番号以降ならOKという設定を使えば次のように分岐処理できます。

'スピル可能かどうかチェックする(バージョン 1911、ビルド 12228.20332)
Sub Formula_test03()
    If Application.Build >= 12228.20332 Then
        Range("H2").Formula2 = "=XLOOKUP(G2,A2:A101,B2:E101,""該当なし"")"
    Else
        'スピル対応不可の場合の代替処理
        Range("H2").Formula = "=IFERROR(VLOOKUP(G2,A2:E101,2,FALSE),""該当なし"")"
        Range("I2").Formula = "=IFERROR(VLOOKUP(G2,A2:E101,3,FALSE),""該当なし"")"
        Range("J2").Formula = "=IFERROR(VLOOKUP(G2,A2:E101,4,FALSE),""該当なし"")"
        Range("K2").Formula = "=IFERROR(VLOOKUP(G2,A2:E101,5,FALSE),""該当なし"")"
    End If
End Sub

スピル機能がないバージョンではVLOOKUPを使うようにしてみました。(安直ですが)
 

【こちらの記事もおすすめです】

 

 

スポンサーリンク
Amazon Excel関連のおすすめ書籍へのリンク!

Amazonクーポンを利用してお得に購入! Amazonギフト券のご利用ならこちらからどうぞ!

Amazon プライム会員なら特典がいっぱい!

まとめ(おわりに)

以上、Excel VBA で動的配列数式や関数をセルに入力するには Range.Formula2 プロパティを使う必要があるということの解説でした。

あわせて、Excel のビルド番号から「スピル機能」に対応しているかどうかを判断して、処理を分岐させる方法についても紹介しました。

くるみこ
くるみこ

いかがでしたか?
新しい Range.Formula2 プロパティについては、新バージョンのExcelで使うためのプロパティですので注意して使いましょう。

ビルド番号でスピル機能に対応しているかどうか判別できるのはうれしいです(^^♪


★★★ ブログランキング参加中! クリックしてね(^^)/ ★★★

【今後の記事について】

今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてる」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m

【検討中の今後の記事内容は・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思っています・・・・
・今後の記事にご期待ください(^^)/

過去記事のサンプルファイルをダウンロードできます

今回記事のサンプル登録はありません! 記事内のコードをコピーしてご利用ください。
データは前回記事にサンプルがありますのでそちらをご利用ください。

過去の記事で使用したサンプルファイルをダウンロードできるようにページを設置していますので、こちら(このリンク先)からご利用ください

タイトルとURLをコピーしました