Application.Evaluateメソッドの使い方について解説します。
Evaluateは Excel VBAのコード上でワークシートの数式を実行するために使用できる便利なメソッドです。知っておくと役に立つことがあるかもしれません。
新しいExcelにスピル機能が導入されたことでワークシート上の数式もスピルするようになりましたが、VBAではスピルしてくれません。
VBAから動的配列を伴う関数を操作する場合、Evaluateメソッドを使うことでうまく対応できそうなのでEvaluateメソッドの使い方を検証していきたいと思います。
Evaluateメソッドって何？初めて聞きました。詳しくお教えてください。
よろしくお願いしますm(__)m
【この記事でわかることは】
・Application.Evaluateメソッドとはどういうものかがわかります
・Application.Evaluateメソッドの使い方がわかります
Application.Evaluateメソッドとは
MSのリファレンス（リンク先）を確認すると、Evaluateメソッドを使うことで、次のようなExcelのオブジェクトを参照することができることが確認できます。
- 数式（VBAのコード上でExcelの数式を実行できます）
- 単一セル（A1 スタイル表記で、参照は常に絶対参照になります）
- 範囲セル（演算子 (コロン、スペース、コンマ) を参照と一緒に使用できます）
- 名前定義
- 外部参照（演算子に ! 使用。別のブックの例：Evaluate(“[BOOK1.XLS]Sheet1!A1”)）
- Chart（グラフ）等の図形オブジェクト
- フォームコントロール（番号または名前を指定できます） 等
- ※引数に指定する文字数の長さは 255 文字以下にする必要があります。
ワークシートのセルで使用する “式” をVBA上で評価(実行)してその結果を返してくれます。
セル内で使用できる数式であれば動的配列でもうまくいけそうですので確認してみます。
Evaluateメソッドの使い方
VBAからワークシート関数を利用するには、通常は WorksheetFunction を使います。ただし、ワークシート上で使える関数をすべてVBAから実行できるわけではありません。
たとえば、VBAにも同じ働きをする関数がある場合、同名のワークシート関数は使用できません。
A1セルに文字列「EXCEL」が入っている状態で、次のコードを動かすと4番目の（ワークシート関数の）LEFT関数でエラーになることが確認できます。５番目もエラーです。
Sub Test01()
Debug.Print Range("A1")
Debug.Print Left(Range("A1"), 4)
Debug.Print Left("EXCEL", 3)
Debug.Print WorksheetFunction.Left(Range("A1"), 2)
Debug.Print WorksheetFunction.Left("EXCEL", 1)
End Sub
２番目と３番目はVBAのLEFT関数、４番目と５番目がワークシート関数の「LEFT」です。
WorksheetFunctionでは「LEFT関数」が使えないことがわかりました。
Evaluateメソッドの書式
Evaluateメソッドの書き方の基本は、= Evaluate(“式”) という書き方です。
省略した簡便な書き方は、= [式] という書き方です。（評価する式を角カッコ [] で囲む)
まずは、先ほどのコードを Evaluate に書き換えた例です。次のコードを見てください。
※ただし２番目と３番目のVBAのLEFT関数は、ワークシート関数に置き換えています。
Sub Test02()
Debug.Print Evaluate("A1")
Debug.Print Evaluate("Left(A1, 4)")
Debug.Print Evaluate("Left(""EXCEL"", 3)")
Debug.Print [Left(A1, 2)]
Debug.Print [Left("EXCEL", 1)]
End Sub
・3つ目までが基本的なEvaluateメソッドの書き方です。
・5と6行目が、Evaluateメソッドの簡略した書き方です。
VBAでセルをA1形式で指定するとき、Range(“A1”) のようにRangeを使って指定します。
それを、Evaluateメソッドで記述する場合、Evaluate(“A1”) または [A1] と記述できます。
簡略のコード例では「WorksheetFunction.」「Range」「””」「Evaluate」などの記述を省略して書くことができています。簡略できるのはすごく楽で助かります。
でも、自分だけでなく第三者がコードを閲覧するような場合、Evaluateメソッドを理解できていない方には可読できないコードとなってしまします。どうしても、Evaluateメソッドの簡略形 [A1] などを使いたい場合は、コード閲覧者にきちんと伝わるように、コメントなどに説明を記しておく必要がありますね。
動的配列数式で使ってみる
新しく追加されたワークシート関数のFILTER関数は、VBAに既存で存在していたFILTER関数と同名の関数です。
ワークシート関数のFILTER関数はワークシート上でスピルする動的配列関数です。
EvaluateメソッドでFILTER関数を使ってみます
では、この記事のデータと数式（下図）をそのまま使って、VBAで同じように結果を取得・表示できるかどうかテストしみましょう。
この画像と同じ数式をEvaluateメソッドで実行し、結果をセルG12 に書き込むコードがこちらです。
Evaluateの引数には、セルに書いた数式の文字列を””で括ってそのまま入れる感じです。
'FILTER関数をEvaluateメソッドの基本書式で記述
Sub Test03()
Dim r '戻り値用変数を用意（動的な２次元配列用）
'セルで使っている数式は =FILTER(A2:E101,(D2:D101<J1)+(D2:D101>=J2),"該当なし")
r = Evaluate("FILTER(A2:E101,(D2:D101<J1)+(D2:D101>=J2),""該当なし"")")
'戻り値は２次元配列なのでUBoundでサイズ（範囲）指定してセルに書き込む
Range("G12").Resize(UBound(r, 1), UBound(r, 2)) = r
End SubセルG12を基準に Resizeプロパティを使ってセル範囲のサイスを拡張して書き込んでいます。
Resizeプロパティは、Rangeオブジェクト.Resize（変更後の行数, 変更後の列数）のように使います。
変更後の行数と変更後の列数はそれぞれ UBound を使って２次元配列から算出しています。
実行結果の画像がこちらです。計算式と同じ結果がセルG12からの範囲に書き込まれました。
Evaluateメソッドを簡略で記述する場合は、[]内にセルに書いた数式の文字列を“”で括ることなくそのまま入れる感じになります。
'FILTER関数をEvaluateメソッドを簡略で記述
Sub Test04()
Dim r '戻り値の動的配列は２次元配列
'セルで使っている数式は =FILTER(A2:E101,(D2:D101<J1)+(D2:D101>=J2),"該当なし")
r = [FILTER(A2:E101,(D2:D101<J1)+(D2:D101>=J2),"該当なし")]
'戻り値は２次元配列なのでUBoundでサイズ（範囲）指定してセルに書き込む
[G12].Resize(UBound(r, 1), UBound(r, 2)) = r
End Subセル範囲に結果を書き込む場合はこのような感じでOKのようですね。
ただし、VBAの場合注意しなければならないのは、書き出し先にすでにデータがあった場合でも構わず上書きしてしまいます。
あらかじめ書き込むセル範囲に既存データが存在するかどうかを確認する処理を入れるなどの対応が必要でしょう。
Evaluateメソッドの注意点
・Evaluateメソッドの場合、エラーが発生しても何も起こらないので気づかないことが多いです。実行してみて初めてエラーに気づきます。
・Evaluateメソッド内の “式” 入力時には自動補完が使えません。文字列だから当然ですね。
・”式” は、ワークシートで先に作ってから貼り付けるなどのようにすればよいと思います。
2次元配列からループ処理で値を取り出す
FILTER関数で絞り込んだ結果を2次元配列で取得できましたが、セル範囲に一括で書き出すのではなく、特定のデータだけ取り出したい場合には次のような処理方法があります。
取得データは2次元配列なので、次元ごとの最小・最大をLBound・UBoundで調べ、For～Nextループを２次元にネストしてひとつずつ値を取り出すコードの例です。
Sub 二次元配列をFor_Nextループで処理する例()
Dim r, x As Long, y As Long
r = [FILTER(A2:E101,(D2:D101<J1)+(D2:D101>=J2),"該当なし")]
For x = LBound(r, 1) To UBound(r, 1)
For y = LBound(r, 2) To UBound(r, 2)
Debug.Print r(x, y) '・・・値の書き出し
Next y
Next x
End Subちなみに、セル範囲から配列に格納した場合の配列要素は、行列ともに「1」から始まります。
なので、ここでの LBound の取得値は「1」なので LBound しなくても「1」としてもOKです。
まとめ（おわりに）
以上、Excel VBA で Evaluateメソッドを使う方法についての解説でした。
新しいExcel の「スピル機能」に対応した動的配列数式が登場してから、Evaluateメソッドを利用する方法が見直された気がします。けしてEvaluateメソッドは新しく登場したものではありません。
Evaluateメソッドについて理解できましたか？
スピルするワークシート関数をVBAで使う場合、WorksheetFunctionでうまくいかなかったらEvaluateメソッドを使えばうまくいけそうです。でも注意して使いましょうね。
[] を使っていたコードは、何となくどこかで見た気がしました。
[] は Evaluateメソッドだったということがよくわかりました(^^♪
★★★ ブログランキング参加中！ クリックしてね(^^)/ ★★★
【今後の記事について】
今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてる」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m
【検討中の今後の記事内容は・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思っています・・・・
・今後の記事にご期待ください(^^)/
過去記事のサンプルファイルをダウンロードできます
今回記事のサンプル登録はありません！ 記事内のコードをコピーしてご利用ください。
データは前回記事にサンプルがありますのでそちらをご利用ください。
過去の記事で使用したサンプルファイルをダウンロードできるようにページを設置していますので、こちら（このリンク先）からご利用ください
