マクロからワークシート関数を利用するには、WorksheetFunctionを使います。
次のコードは、セル範囲A1:A5をSUM関数で合計します。
Sub Sample1()
Dim Result As Long
Result = WorksheetFunction.Sum(Range("A1:A5"))
MsgBox Result
End Sub
WorksheetFunctionは、ワークシート関数群をメンバーに持つコレクションですが、ワークシート上で使える関数をすべてVBAから使用できるわけではありません。
たとえば、VBAにも同じ働きをする関数がある場合、同名のワークシート関数は使用できません。
次のコードでは、Left関数がエラーになります。
Sub Sample2()
MsgBox WorksheetFunction.Left("VBA", 1)
End Sub
また、中には何らかの理由で使用できない関数もあります。
たとえば、ピボットテーブル内のデータを参照するワークシート関数にGETPIVOTDATA関数がありますが、これもWorksheetFunctionからは使用できません。
GETPIVOTDATA関数は次のように使います。
=GETPIVOTDATA("金額",B4,"名前","山田")
この式は、ピボットテーブルにレイアウトした[金額]フィールドで[名前]フィールドが「山田」の合計を返します。
「B4」はピボットテーブルが含む任意のセルです。
この便利なGETPIVOTDATA関数は、残念ながらWorksheetFunctionに含まれていません。
でも大丈夫。とっておきの方法を使えば、どんな関数でもVBAから使用できるんです。
その秘密兵器は、Evaluateメソッドです。
ApplicationオブジェクトとWorksheetオブジェクトが持つEvaluateメソッドは、引数に指定した"式"を評価して結果を返す関数です。
たとえば、次のコードは、Sample1と同じようにセル範囲A1:A5をSUM関数で合計します。
Sub Sample3()
Dim Result As Long
Result = Evaluate("SUM(A1:A5)")
MsgBox Result
End Sub
SUM関数の引数が、Rangeオブジェクトではなく、ワークシート上のように文字列でアドレスを指定している点に留意してください。
このEvaluateメソッドを使えば、GETPIVOTDATA関数だってVBAから実行できます。
Sub Sample4()
Dim Result As Long
Result = Evaluate("GETPIVOTDATA(""金額"",B4,""名前"",""山田"")")
MsgBox Result
End Sub
Evaluateメソッドが評価する"式"は、ワークシート関数だけではありません。
Evaluate("1+2")はもちろん、Evaluate("A1+B3")といったセルの参照式もOKです。
さらにEvaluateメソッドは、Evaluate("〜")と書かなくても角カッコを使って、[〜]のように代用することができます。
上記のSample3は、次のように書けます。
Sub Sample5()
Dim Result As Long
Result = [SUM(A1:A5)]
MsgBox Result
End Sub
たまに掲示板などで見かける
Sub Sample6()
Dim buf As Long
buf = [A1]
[B1] = buf * 2
End Sub
のようなセル指定で、[A1]はEvaluate("A1")の省略形なのです。
そうとは知らずに使っている人も、かなりいるようですが…。
このように、驚くほど便利なEvaluateメソッドですが、使用には注意が必要です。
その理由は『Evaluateメソッドが、ほとんど知られていない』ということです。
VBAでセルを指定する基本は、Range()とCells()です。
雑誌、書籍、スクール、Webと、どの教材でも必ず教わる基本中の基本です。
しかし、Evaluateメソッドを解説しているコンテンツは、ほとんどありません。
したがって、多くのユーザーは
Sub Sample5()
Dim Result As Long
Result = [SUM(A1:A5)]
MsgBox Result
End Sub
や
Sub Sample6()
Dim buf As Long
buf = [A1]
[B1] = buf * 2
End Sub
といったコードを見ても、角カッコが何の意味なのかわからないと思います。
角カッコがEvaluateメソッドの省略形であるということは、おそらく多くのユーザーが理解していないでしょう。
自分だけが使うマクロならともかく、第三者がコードを閲覧する可能性があるのなら閲覧者に疑問を抱かせるような、そんな可読性の低いコードを書くべきではありません。
Evaluateメソッドを角カッコで省略した[A1]などを使うときは、それがEvaluateメソッドであり、さらに省略した記述だという旨をコメントなどで、コードの閲覧者にしっかりと伝えるようにしましょう。
