Excel VBA 動的配列には Range.Formula2

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

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

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

くるみこ

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

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

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

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

Excel VBAで数式を一括入力し値に変換するツール【汎用版】

Excel VBA実行中やデータ入力処理中などに重い計算式がたくさん設定されていると、再計算が頻繁に繰り返されて思うように作業が進まないことって無いですか？そんな悩みをこの記事で解決します。数式を一括入力して値に変換する汎用ツールの解説です

www.kurumico.com

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

Formula2 と Formula の違い

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

Excel VBA XLOOKUP関数を使う方法

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

www.kurumico.com

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

www.kurumico.com

まとめ（おわりに）

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

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

くるみこ

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

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

【今後の記事について】

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

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

スポンサーリンク

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

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

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

【Excel VBA】サンプルファイル_ダウンロードのページ

【Excel VBA】の記事内で使ったサンプルファイルを（Zipファイルで）ダウンロードできるようにしました。記事内では細かい部分の解説が不十分ですが、実際にファイルをダウンロードして実行することで使えるのかどうか見ていただけたら幸いです。

www.kurumico.com