Evaluateメソッド

  1. Home
  2. Excel
  3. VBA
  4. Excel VBA Tips

VBAの中でも、ほとんど知られていないのがEvaluateメソッドです。Evaluateメソッドは、Applicationオブジェクト、Worksheetオブジェクト、Chartオブジェクトのメンバーですが、一般的にApplicationオブジェクトのメソッドとして使うことが多いので、本稿でもそれを解説します。EvaluateメソッドはExcel 97の時代からある古参メソッドです。それでも、ほとんど知られていない理由は、あまり利用価値がないからです。Evaluateメソッドでできることのほとんどは、他のプロパティなどで実現可能ですし、そちらの方が基本的で解説も多いです。しかし、ときどきこのEvaluateメソッドを、そうとは知らずに使っている方をネットの掲示板などで散見しますし、ごく希に、Evaluateメソッドでなければ実現不可能なこともあります。なにより、選択肢は多い方がよいので、本稿で詳しく解説します。ただし、乱用すると可読性が著しく低下するので注意してください。

何をするメソッドか

Evaluateメソッドとは、いったい何なのでしょう。まずはヘルプを見てみます。

Application.Evaluate メソッド 
使用する名前を、オブジェクトまたは値に変換します。

構文
式.Evaluate(Name)
式   Application オブジェクトを表す変数。

"使用する名前"というのは、Evaluateメソッドに指定する引数Nameのことです。本来は上記のように「Application.Evaluate(Name)」と書くのが文法的に正確なのでしょうけど、Applicationは省略可能なので、ただ普通に「Evaluate(Name)」と書きます。さて、ヘルプの続きを見てみましょう。

パラメーター
Name    Excelの名前付け規則に従って、対象オブジェクトの名前を指定します。

「名前付け規則」といえば、セルに独自の"名前"を定義する「名前機能」が思い浮かびますけど、そうではありません。また「オブジェクトの名前」ということなら、たとえばセルはRangeオブジェクトですし、ワークシートはWorksheetオブジェクトのように、オブジェクトの名称のように感じますが、それも違います。いや、両者とも間違ってはいません。もちろん、それらも含みますが、Evaluateメソッドに指定できるものは、それだけではありません。Evaluateメソッドに指定する引数とは、ザックリ言うと「セルの中に入力したとき、数式として機能する文字列」のことです。いわば"数式"ですね。セルには、さまざまな数式を入力できます。

「=A1」などのように、セルのアドレスや名前を指定する仕組みが"セル参照"です。セル参照を使った数式を「参照式」などと呼びます。もちろん、"セル参照"を使わない数式だって存在します。同様に、ワークシート関数もセルに数式として入力することで何らかの働きをします。

こうした数式を引数に指定して、その数式の結果(値)を得たり、その数式が返すオブジェクトを表すのがEvaluateメソッドです。ちょっとやってみましょう。

Sub Macro1()
    MsgBox Evaluate("SUM(A1:A3)")
End Sub


Sub Macro2()
    Evaluate("INDEX(A1:A3,2)") = 100
End Sub

こんなことも可能です。

Sub Macro3()
    Evaluate("A1").Copy Evaluate("A3")
    MsgBox Evaluate("A2").Value
End Sub

CopyもValueも、セル(Rangeオブジェクト)のメソッドやプロパティです。Evaluateメソッドがセル(Rangeオブジェクト)を返すので、こういう使い方もできます。できますけど、これを見て「はぁ？これってRangeでよくね？何が違うの？」って思いませんか？そのとおりです。こんなことをするのなら、Range("A1")みたいに書けばいいです。それがVBAの基本です。SUM関数やINDEX関数も同様です。次のように書くのが、VBAのセオリーです。

Sub Macro4()
    MsgBox WorksheetFunction.Sum(Range("A1:A3"))
    Range("A1").Offset(1, 0) = 100
End Sub

ここまで紹介した例でしたら、VBAのセオリーどおり、RangeやWorksheetFunctionなどを使ってくださいな。実務の現場では、第三者が理解できる易しいコードが正義です。間違っても、みんなが知らない書き方をして"腕自慢"をしないように。とはいえ、後述しますが、このEvaluateメソッドでなければ実現できないこともありますので、選択肢の一つとして、使えるようになっていてください。

Evaluateメソッドの簡略形

ヘルプには、次の一文も記載されています。

メモ
Evaluateメソッドの代わりに角かっこを使っても、同じ結果が得られます([A1:C5]など)。

つまり、下記のコードは、どちらも同じことを表しています。

Sub Macro5()
    MsgBox Evaluate("SUM(A1:A3)")
    Evaluate("INDEX(A1:A3,2)") = 100
    Evaluate("A1").Copy Evaluate("A3")
    MsgBox Evaluate("A2").Value
End Sub

Sub Macro6()
    MsgBox [SUM(A1:A3)]
    [INDEX(A1:A3,2)] = 100
    [A1].Copy [A3]
    MsgBox [A2].Value
End Sub

この[A1]という書き方ですね、ときどきネットの掲示板などで勘違いしている使い方は。たとえば本来ならば「Range("A1") = 100」と書くところを「[A1] = 100」のようにして「セルを表すときは、Range("A1")のほかに[A1]という書き方もある」って誤解しているケースです。違いますからね。[A1]というのは、セルを表す書き方のひとつではなく、Evaluateメソッドの簡略記述です。そして、Evaluateメソッドは「セルを表す」ためのものではありません。[A1]でセルA1を表すなんてのは、Evaluateメソッドが持つ動作のうち、ごくごく一部の使い方ですし、むしろ[A1]みたいな使い方は意味がないです。普通にRange("A1")って書いてください。余談ですけど「Range("A1")って入力するより、文字数の少ない[A1]の方が、入力するのが速い」っていう意見を、大昔に聞いたことがあります。なるほどですね～、きっとその方はキーボードに不慣れなのでしょうね。Range("A1")って打つのに1分くらいかかるんでしょうかね。パソコンを始めたばかりの超シロウトさんなのでしょう。だったら、VBAにチャレンジするよりも、まずタッチタイピングの練習をお勧めします。ああ、ちなみに。いま実際に計測して確認しましたが、Range("A1")よりも[A1]の方が、マクロの実行速度が、約2倍遅いです。ご存じでしたか？遅いマクロをお望みなら、どうぞ[A1]と書いてください。

さてさて、上記のように、Evaluateメソッドは[]で代用できます。しかし、両者の記述には大きな違いがありますので、注意が必要です。「Evaluate()」の括弧内には、文字列を指定します。もっと正確にいうなら、VBAの文法ルールに則った文字列形式の値です。対して、簡略記述で[]の中に記述するのは、セルに入力して動作する数式です。数式そのものというか、数式だけというか。数式を"文字列形式"で記述するのではありません。[]の中はセル内の数式ルールに従います。簡単に言えば、[]内に記述されている文字列を、そのまま「=」をつけてセルに入力したとき、数式として機能しなければいけません。

まぁ、簡単な数式でしたら、両者に違いがないものもあります。

Sub Macro7()
    Evaluate("A1") = Evaluate("SUM(B1:B3)")
    ''↑↓これらは同じ
    [A1] = [SUM(B1:B3)]
End Sub

では、次のケースで考えてみましょう。

これと同じ結果になるようなマクロを、Evaluateメソッドで作ってみます。まずは、分かりやすい簡略記述[]から。これは簡単です。セル内の数式を、そのまま書けばいいです。

Sub Macro8()
    Range("C2") = [COUNTIF(A2:A6,"田中")]
End Sub

さて、問題はEvaluate()の方です。これは、次のように書かなければなりません。

Sub Macro9()
    Range("C2") = Evaluate("COUNTIF(A2:A6,""田中"")")
End Sub

「""田中""」部分の意味が分からない方は、下記のページをご覧ください。

ダブルコーテーションの表示

ではもし、この「田中」が変数に入っていたとしましょう。すると、こうなります。

Sub Macro10()
    Dim A As String
    A = "田中"
    Range("C2") = Evaluate("COUNTIF(A2:A6,""" & A & """)")
End Sub

あるいは、こんな書き方もできますね。

Sub Macro11()
    Dim A As String
    A = """" & "田中" & """"
    Range("C2") = Evaluate("COUNTIF(A2:A6," & A & ")")
End Sub

いかがですか？多くのビギナーにVBAを教えてきた経験からいうと、ビギナーは「文字列を結合して何かの文字列を作る」というイメージが、メチャクチャ苦手です。発想も技術もありません。まして今回は、ダブルコーテーション("")を文字列として扱わなければならないので、難易度は256倍に跳ね上がります。こういうコードは、自分で作るのも難しいでしょうし、読み解くのも難儀します。ああ、だったら、簡単に書ける[]を使えばいいのでは？やってみましょう。

Sub Macro12()
    Dim A As String
    A = "田中"
    Range("C2") = [COUNTIF(A2:A6,A)]
End Sub

失敗します。正常に動作しません。なぜだか分かりますか？簡略記述の[]内は「セル内の数式ルールに従う」からです。「COUNTIF(A2:A6,A)」の"A"は、変数だと認識されません。だって変数って、VBAの仕組みでしょ。ある意味[]内は治外法権みたいなものです。だったら、上でやったように文字列結合すれば……いやいや、ちょっと待ってください。[]の中は、文字列形式じゃないんですよ。[]の中で文字列結合はできません。もし、文字列結合したいのなら、[]ではなくEvaluate()を使ってください。

最後に、もしEvaluateメソッドがエラーになったらどうなるのかを解説します。これも、VBAとしては、ちょっと変わった挙動なので注意してください。Evaluateメソッドがエラーになる可能性は、次のパターンが考えられます。

  1. 指定した数式に、何らかの文法的な間違いがある
  2. 正しい数式を指定したが、数式の結果がエラーになる

まずは、成功するパターンから。

Sub Macro13()
    Range("C1") = Evaluate("SUM(A1:A3)")
End Sub

[]でも同じなので、Evaluate()の方だけやりました。では、まず「1.数式に文法的な間違いがある」ケースを試してみましょう。

Sub Macro14()
    Range("C1") = Evaluate("SUN(A1:A3)")
End Sub

SUM関数のスペルを間違えました。結果は次のとおりです。

え？ん？えっと、いろいろ予想外な結果です。まず重要なことですが、マクロ自体はエラーになりませんでした。いわゆるマクロの実行時エラーで止まりません。「#NAME?」というのは知ってます。セルに入力した数式で、今回のように関数名が間違っているときのエラーです。ってことは、セルに「=SUN(A1:A3)」という数式が代入されたのかと？いや、数式バーを見る限り、そうでもなさそうです。ってことは、セルに「#NAME?」という文字列が代入されたのでしょうか。ちょっと試してみます。

隣のセルD1に「=LEFT(C1,1)」という数式を入力してみました。セルC1に文字列が入力されているのなら、これで「#」という文字が抜き出せるはずですが、結果はエラーです。ってことは、セルC1に入っているのは文字列ではないと。じゃぁ、いったい何なのかと。そもそも「Evaluate("SUN(A1:A3)")」は何を返したのでしょう。次のようにして確認してみました。

Sub Macro15()
    Dim A As Variant
    A = Evaluate("SUN(A1:A3)")
    MsgBox TypeName(A)
End Sub

これは「エラー値」という特別な値です。ちなみに、簡略記述の[]でも同じ結果でした。

Sub Macro16()
    Dim A As Variant
    A = [SUN(A1:A3)]
    MsgBox TypeName(A)
End Sub

ちょっと嫌な予感がするので、次の「2.合ってるけどエラーになる数式」を試してみます。

LARGE関数は「○番目に大きい数値」を返す関数ですが、3つの数値に対して"4番目"を指定しているのでエラーになります。

Sub Macro17()
    Dim A As Variant
    A = Evaluate("LARGE(A1:A3,4)")
    MsgBox TypeName(A)
End Sub

Sub Macro18()
    Dim A As Variant
    A = [LARGE(A1:A3,4)]
    MsgBox TypeName(A)
End Sub

これでハッキリしました。何らかの理由でEvaluateメソッドがエラーになるとき、マクロ自体が実行時エラーにはならず、Evaluateメソッドが「エラー値」を返すと。ってことは、これってけっこう面倒くさいですね。たとえば、次のようにEvaluateメソッドの結果を変数に入れるとしましょう。

Sub Macro19()
    Dim A As Long
    A = [SUN(A1:A3)]
    MsgBox A
End Sub

SUM関数の結果は数値です。それを受け取る変数ですから、Long型にしました。何も間違っていません。でも、SUM関数のスペルを間違えています。つまり、Evaluateメソッドはエラーになります。

なぜ、変数に代入する行でエラーになるか分かりますか？Long型は数値しか格納できませんが、「エラー値」は数値ではないです。だから「型が一致しません」って怒られます。では、数値でも、エラー値でも、何でも格納できるバリアント型で変数を宣言してみます。

Sub Macro19()
    Dim A As Variant
    A = [SUN(A1:A3)]
    MsgBox A
End Sub

すると今度は、MsgBoxの行がエラーになりました。こちらもエラーメッセージは「型が一致しません」です。この意味は分かりにくいかもしれませんね。MsgBoxは関数です。そして、その引数には"文字列型"を指定するのがルールです。でも「MsgBox 100」はエラーになりませんよね。このときは、VBAが数値型の100を文字列型の"100"に内部で変換してくれているからです。でも、エラー値は文字列型に変換できません。だから「型が一致しません」です。

エラーの意味が分かったところで、じゃぁ「Evaluateがエラーになったかどうか」を、どう判定すればいいのでしょう。たとえば、こんな感じですかね。

Sub Macro20()
    Dim A As Variant
    A = [SUN(A1:A3)]
    If IsError(A) Then
        MsgBox "エラーです"
    Else
        MsgBox A
    End If
End Sub

受け取る変数をバリアント型にしておいて、返り値を格納したあとで、変数がエラー値かどうかを判定しています。あるいは、次のような手もありますね。

Sub Macro21()
    Dim A As Long
    On Error Resume Next
    A = [SUN(A1:A3)]
    If Err.Number > 0 Then
        MsgBox "エラーです"
    Else
        MsgBox A
    End If
    On Error GoTo 0
End Sub

そもそも「エラー対策」って、ほとんどの人が苦手としていますけど、そんな中でも今回のケースは、ちょっと難易度が高いと感じます。受け取る変数の型によって、エラーをキャッチするタイミングを検討しなければなりません。みなさん、がんばってくださいね。

Evaluateメソッドでないと不可能なこと

Evaluateメソッドで実現できることは、たいていの場合、ほかのプロパティなどでも可能です。だから、長いVBAの歴史の中で、あまり注目されなかったのでしょう。しかし、ごく希に「Evaluateメソッドを使わないと実現できない」動作も存在します。それは「WorksheetFunctionで呼び出せない関数をマクロ内で使う」ようなケースです。

ここ数年、Excelには「スピル系のワークシート関数」が続々と追加されています。

【2019年8月】
SORT, SORTBY, FILTER, UNIQUE, XMATCH, XLOOKUP, RANDARRAY, SEQUENCE

【2022年3月】
TEXTBEFORE, TEXTAFTER, TEXTSPLIT, VSTACK, HSTACK, TOROW, TOCOL, WRAPROWS, WRAPCOLS, TAKE, DROP, CHOOSEROWS, CHOOSECOLS, EXPAND

【2023年11月】
GROUPBY, PIVOTBY, PERCENTOF

【2024年5月】
REGEXTEST, REGEXEXTRACT, REGEXREPLACE

本来、マクロの中でワークシート関数を使うときは、WorksheetFunctionクラスを使います。

Sub Macro22()
    MsgBox WorksheetFunction.Sum(Range("A1:A4"))
End Sub

WorksheetFunctionクラスでは、すべてのワークシート関数を呼び出せるわけではありません。たとえば、IF関数やOFFSET関数は呼び出せません。それでも、新しいワークシート関数が登場すると(多少の期間的なズレもありましたが)、WorksheetFunctionクラスに追加されてきました。上記のように、ここ数年で追加された「スピル系のワークシート関数」であっても、2019年8月期のFILTER関数やXLOOKUP関数などはWorksheetFunctionで呼び出せます。しかし、本稿執筆時点(2024年5月)になっても、2022年3月期以降のワークシート関数は、いまだWorksheetFunctionクラスに追加されていません。いくらなんでも、2年は長すぎます。これ、もしかしてMicrosoftは、新しく登場したワークシート関数を追加しない方針なのかもしれません(知らんけど)。だとしたら、非常にショックです。残念でなりません。だって、2022年3月期のVSTACK関数やTAKE関数やCHOOSECOLS関数なんて、マクロで使えたらメチャクチャ便利です。最近追加された正規表現系の関数だって、同じ置換をVBAだけでやるよりも、ワークシート関数を呼び出す方が簡単に決まっています。だけど、それなのに、2年待っても追加されません...しょんぼりへにょんです。だったら、もういいです！WorksheetFunctionで呼び出せないなら、Evaluateメソッドで使っちゃいます。

ということで、使用例をいくつかご紹介します。まずは鉄板から。

3つのテーブルがあります。名前はそれぞれ「Data1」「Data2」「Data3」です。ここでは解説のため、同じシート内に存在するとしますが、実際には別のシートに作った複数のテーブル、みたいなイメージです。これらのテーブルを、1つのリストに合体させます。使うのはVSTACK関数です。

Sub Macro23()
    Dim A As Variant
    A = [VSTACK(Data1,Data2,Data3)]
    Range("M2").Resize(UBound(A, 1), UBound(A, 2)) = A
End Sub

笑っちゃうほど簡単です。では次。

ハイフン(-)で区切られたデータから、真ん中の数値部分だけを抜き出します。

Sub Macro24()
    Dim i As Long
    For i = 2 To 7
        Cells(i, 2) = Evaluate("TEXTSPLIT(""" & Cells(i, 1) & """,""-"")")(2)
    Next i
End Sub

TEXTSPLIT関数の第1引数には、分割前の文字列を指定します。本来なら、Cells(i, 1).ValueとかCells(i, 1).Textのようにすれば文字列形式の値を取得できますが、TEXTSPLIT関数に渡すときは、明示的にダブルコーテーション("")で囲ってやらないとエラーになるようです。

Evaluate("TEXTSPLIT(""" & Cells(i, 1) & """,""-"")")(2)

詳しく検証していませんけど、何となくこれ、TEXTSPLIT関数側の事情みたいな気がします。もしかしたら、何かの関数でも変換できるかもしれません。じゃ、このデータが次のようになっていたらどうでしょう。

こうなったら正規表現で一発ですね。

Sub Macro25()
    Dim i As Long
    For i = 2 To 7
        Cells(i, 2) = Evaluate("REGEXEXTRACT(""" & Cells(i, 1) & """,""\d+"")*1")
    Next i
End Sub

結果は同じなので画像は割愛します。REGEXEXTRACT関数の結果は文字列を返すので、最後に「*1」して数値に変換しています。

ちなみに、2019年8月に追加された"Excel史上最強"のFILTER関数は、WorksheetFunctionから呼び出せます。呼び出せますけど、Evaluateメソッドを使わないとエラーになります。

原因は「Range("Data[名前]") = "田中"」部分が、VBAではスピらないからです。スピルというのは、セル内で計算するときの仕組みです。スピルはセル内でしか起こりません。だから、次のようにしてやります。

Sub Macro27()
    Dim A
    A = WorksheetFunction.Filter(Range("Data"), [Data[名前] = "田中"])
    Range("E2").Resize(UBound(A, 1), UBound(A, 2)) = A
End Sub

Evaluateメソッドは、使いどころが限られるメソッドです。しかし、ごく希に"Evaluateメソッドでないとできない"ケースもありますので、奥の手として覚えておくといいでしょう。ただし、くれぐれも [A1] = 100 みたいな意味のない使い方はしないでください。そして、実務のマクロでは、後任者にしっかりと「Evaluateメソッドとは何か」を解説してください。解説できない人は、使っちゃダメですよｗ