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でセルに関数や数式を入力する
まとめ（おわりに）
過去記事のサンプルファイルをダウンロードできます

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

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

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

Formula2 と Formula の違い

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

Excel VBA XLOOKUP関数を使う方法

VBAでExcel新関数のXLOOKUP関数を使う方法です。XLOOKUP関数はスピル機能を有しているので戻り値範囲によって戻り値は「動的配列」となることをサンプルを使って解説しています。記事で使った簡単なサンプルファイルを登録しています。

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

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

【結果画像】

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

Formula2 を使ってみます

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

【結果画像】

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を使うようにしてみました。（安直ですが）
　

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

Excelブック内全数式を一括取得・貼り付けするVBA汎用ツール

VBAでExcelブック内のすべての数式をシートに書き出す方法を解説します。そして、シートに書き出し保存しておいた数式を元の場所に貼り付ける処理も同時に解説します。このツールで数式をバックアップしておくことが出来るのでその活用法を解説します

まとめ（おわりに）

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

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

くるみこ

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

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

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

にほんブログ村

Visual Basicランキング

【今後の記事について】

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

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