ExcelVBAで「文字列として組み立てた数式を、その場で計算したい」と思ったことはないでしょうか。たとえば「A1/B1」のような数式をセルに書き込まずに結果だけ知りたい場合や、動的に生成したSUMPRODUCT数式を実行したい場合などです。
こうした場面で活躍するのが Application.Evaluate メソッドです。この記事では、Evaluateの基本的な使い方から、角括弧[ ]記法との違い、実務で使える応用例、エラー処理の注意点までを、コピペで使えるサンプルコード付きで解説します。
Application.Evaluateとは
Application.Evaluate は、文字列で渡した数式やセル参照をExcelに計算させ、その結果をVBA側で受け取るメソッドです。ワークシート関数をそのままVBAから呼び出す WorksheetFunction とは異なり、セルに数式を入力したのと同じ形式の文字列をそのまま評価できるのが特徴です。
基本構文は次のとおりです。
結果 = Application.Evaluate("数式文字列")
戻り値の型は数式の内容によって変わるため、受け取る変数は基本的に Variant 型で宣言しておくと安全です。
基本的な使い方
まずはシンプルな数式を計算してみましょう。
Sub EvaluateSample()
Dim result As Variant
result = Application.Evaluate("1+2*3")
MsgBox result ' 7 が表示される
End Sub
セル参照を含む数式もそのまま渡せます。ワークシート関数もExcelの数式と同じ書き方で使用できます。
Sub EvaluateWithRange()
Dim result As Variant
result = Application.Evaluate("SUM(A1:A10)")
MsgBox result
End Sub
WorksheetFunction.Sum(Range("A1:A10")) と書いても同じ結果になりますが、Evaluateは数式を「文字列」として渡すため、後述するように実行時に数式そのものを組み立てられるという大きなメリットがあります。
角括弧「[ ]」記法との違い
Evaluateには、角括弧を使った省略記法があります。
Sub BracketSample()
Dim result As Variant
result = [SUM(A1:A10)]
MsgBox result
End Sub
[SUM(A1:A10)] は Application.Evaluate("SUM(A1:A10)") とほぼ同じ意味です。短く書ける反面、以下の点に注意が必要です。
- 角括弧内の文字列は実行時に組み立てることができない（コード上に固定で書く数式専用）
- 変数を埋め込みたい場合は文字列連結ができないため、動的な数式には使えない
つまり、固定の数式をサッと書きたいときは角括弧記法、変数やループで数式を組み立てたいときは Application.Evaluate を使う、という使い分けが基本になります。
実践例1：動的に組み立てた数式を計算する
最終行が可変のデータに対して、条件付きで合計を計算したいケースを考えます。ここでは「B列がすべて『完了』の行だけ、C列を合計する」処理をSUMPRODUCTで実現します。
Sub DynamicSumProduct()
Dim lastRow As Long
Dim formulaStr As String
Dim result As Variant
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
formulaStr = "SUMPRODUCT((B2:B" & lastRow & "=""完了"")*(C2:C" & lastRow & "))"
result = Application.Evaluate(formulaStr)
MsgBox "完了ステータスの合計: " & result
End Sub
データ量に応じて lastRow を求め、その値を文字列連結で数式に埋め込んでいる点がポイントです。ワークシート関数だけでは書けない、実行時に変化するレイアウトに対応した集計処理が実現できます。
実践例2：数式がエラーになるかを事前にチェックする
セルに数式を書き込む前に、その数式がエラーになるかどうかを確認したい場合にもEvaluateが便利です。IsError 関数と組み合わせることで、安全に判定できます。
Sub CheckFormulaError()
Dim formulaStr As String
Dim result As Variant
formulaStr = "A1/B1"
result = Application.Evaluate(formulaStr)
If IsError(result) Then
MsgBox "この数式はエラーになります: " & formulaStr
Else
MsgBox "計算結果: " & result
End If
End Sub
B1が0や空欄の場合、result には #DIV/0! に相当するエラー値が入りますが、Evaluate はエラーで処理を止めることなく結果を返してくれるため、IsError で安全にチェックできます。大量の数式を自動生成するマクロで、書き込み前のバリデーションとして活用できます。
WorksheetFunctionとの違い
Application.WorksheetFunction も似たような目的で使われますが、両者には明確な違いがあります。
| 項目 | Application.Evaluate | Application.WorksheetFunction |
|---|---|---|
| 渡し方 | 数式を文字列で渡す | 引数をVBAの値・オブジェクトで渡す |
| 動的な数式生成 | 得意（文字列連結で組み立て可能） | 苦手（関数ごとに引数を用意する必要あり） |
| エラー時の挙動 | エラー値を返す | 実行時エラーが発生することが多い |
| 使えない関数 | 一部の配列数式は挙動に癖がある | セル参照専用の一部関数は使えない |
「決まった関数を確実に呼びたい」ときはWorksheetFunction、「文字列として数式を組み立てたい」「エラーになっても止めたくない」ときはEvaluate、という使い分けを意識すると迷いにくくなります。
注意点
- Evaluateに渡す文字列には、シート名やセル参照の誤りが混入しやすいため、
formulaStrを組み立てた後にDebug.Print formulaStrでイミディエイトウィンドウに出力し、実際の数式を目視確認する習慣をつけると安全です。 - シートをまたいだ参照を評価したい場合は、
Application.Evaluateではなく対象シートのWorksheet.Evaluateメソッドを使うと、シート名の指定漏れによる誤動作を防げます。 - 巨大な範囲や複雑な配列数式を頻繁にEvaluateで評価すると処理が重くなることがあるため、ループ内で多用する場合は必要な範囲だけに絞り込みましょう。
まとめ
Application.Evaluate を使うと、文字列として組み立てた数式をその場で計算し、結果をVBA側で受け取ることができます。固定の数式なら角括弧[ ]記法、変数を使った動的な数式なら Application.Evaluate という使い分けを覚えておくと、データ量が変化する集計処理や、数式の妥当性を事前チェックするマクロを効率よく作れるようになります。ぜひ実務のマクロに取り入れてみてください。
コメント