Excel 365以降では、スピル機能（Spill Formula）を使って複数の値を一括で返す配列数式が利用可能です。
この機能はVBAからも活用することができ、Application.Evaluateを使えばワークシート関数の配列演算結果を、直接VBAで受け取ることができます。
本記事では、VBAでスピル形式の数式を評価し、配列として取得・活用する方法をご紹介いたします。
目次
想定される活用シーン
- ワークシート関数の計算結果を一括で2次元配列として扱いたい。
- VBA内で複雑な演算処理をExcel関数で簡潔に表現したい。
- マクロ内で軽量かつ高速な数式処理を実現したい。
VBAコードの例
以下のコードでは、=10*{1,2;3,4} という配列数式をEvaluate関数で評価し、その結果を2次元配列として取得し、即座に処理（デバッグ出力）しています。
Sub CalculateSpillFormula()
Dim calcResult As Variant
' スピル形式の配列数式を評価
calcResult = Application.Evaluate("=10*{1,2;3,4}")
' 評価結果の表示（例：デバッグウィンドウ）
Debug.Print "1行目: "; calcResult(1, 1), calcResult(1, 2)
Debug.Print "2行目: "; calcResult(2, 1), calcResult(2, 2)
End Sub
処理の結果
上記のコードでは、配列 {1,2;3,4} に 10 を掛けて以下のような2次元配列が生成されます：
| 列1 | 列2 | |
|---|---|---|
| 行1 | 10 | 20 |
| 行2 | 30 | 40 |
ポイント解説
| 処理内容 | 解説 |
|---|---|
Application.Evaluate | Excelのワークシート関数や式を、VBA内で評価する関数です。 |
{1,2;3,4} | 配列リテラル（スピル形式）。行区切りはセミコロン;、列区切りはカンマ,。 |
calcResult(行, 列) | VBAでは1から始まる2次元配列として取得されます。 |
応用のヒント
- 数式の中で
SEQUENCEやTRANSPOSE関数なども利用可能です（Excel 365以降）。 - VBA内で計算用に使うだけでなく、セル範囲に書き込むことも可能です：
Range("B2:C3").Value = calcResult
まとめ
VBAでスピル形式の数式を使いたい場合は、Application.Evaluate関数を活用することで、Excelの配列数式のパワーをVBA内でも簡単に取り込むことができます。
大量のループ処理を省略できるため、パフォーマンスも良好で、ワークシート関数の強みをそのまま活かすことが可能です。
シンプルな配列演算から応用的な数式処理まで、ぜひこのテクニックを取り入れて、VBAの効率化を実現してください。
