今回はExcel VBAにおける伝家の宝刀。Application.Evaluateメソッドを紹介します。
はじめに
私のフォロワーさんたちは、私が #VBAクイズ や #ワンライナー に答えるときに、角括弧を使ったVBAで回答しているのを見たことがあるかもしれません。
結構使ってる割にはちゃんとした解説をしたことがないので、一度しっかり説明しておかなくてはならないと常々思っておりました。
いつもネタコードとして登場するので、自分には関係ないと思われている方も多いかも知れませんが、実はメチャクチャ便利なので知っていると役に立つことがあるかもしれません。
Application.Evaluateメソッドとは
Evaluateについて
Evaluateは、Excel VBAでのみ使えるVBAコード上からExcelの数式を実行するための関数です。
（Microsoft Excel 16.0 Object Library を参照設定すればWordやOutlookからでも使えます）
Evaluateを使うことで、Excel上の様々なオブジェクトを参照することが出来ます。
- 数式
- 単一セル
- 範囲セル
- 名前定義
- 外部（別のブック）
- Chart等の図形オブジェクト
- フォームコントロール等
※さすがに後半のような使い方をしているところは見たことがないので、今回は省略します。
よく使われるのは、数式、セル、名前定義くらいでしょう。
Evaluateの基本文法
Evaluateの書き方には2種類あり、v = Evaluate("A1")という一般的な関数の呼び出し方と、v = [A1]という特殊な記法があります。
ワンライナーでは短くするために[式]と書きますが、Excel.Application.Evaluate("式")のように書いたほうが汎用性も安定性も高いです。(省略して困るのはExcel以外のVBAから使う時くらいですが)
まず、最小限の利用例を紹介します。
サンプルデータ
| A | B | C | |
|---|---|---|---|
| 1 | a1 | 1 | 4 |
| 2 | a2 | 2 | 5 |
| 3 | a3 | 3 | 6 |
Rangeを返すEvaluate
Sub Test1_1_1() Debug.Print Evaluate("A1") Debug.Print [A1] Debug.Print TypeName([A1]) End Sub
結果
a1 a1 Range
単純にセルA1の値を表示しているだけです。
戻り値の型はRange型ですが、デフォルトプロパティがあるので.Valueとかは省略可能です。
これだけ見ると Range("A1")の代わりにしか見えません。
どちらの記法でも同じ結果となっていますが、角括弧を使う書式の方は、究極にタイピング数を減らせるというということで一部で人気です。
ただ、ブック・シートが明示されていないので、Range同様ActiveWindow.ActiveSheetが対象になります。このままでは不具合の元となりますので注意して使って下さい。
戻り値を変数に入れて使ってみる
Sub Test1_1_2() Dim rng As Range Set rng = Evaluate("A1") Debug.Print rng.address, rng.Value Set rng = [A1] Debug.Print rng.address, rng.Value End Sub
結果
$A$1 a1 $A$1 a1
やっぱり Set rng = Range("A1")の代わりにしか見えませんね。
でも、これはExcelワークシートの「数式」と同じ仕様だから当然なんです。
ここだけの話、実はExcelワークシートの数式も戻り値には型があり、Range型を返すことがあります。（計算中はRangeですが、セルに表示する段階で文字列や数値に変化しています。）
計算を行うEvaluate
Sub Test1_1_3() Debug.Print Evaluate("1+2+3") Debug.Print Evaluate("B1+C1")'1+4 Debug.Print TypeName(Evaluate(1 + 2 + 3)) End Sub
結果
6 5 Double
書いてあるとおり、B1の1とC1の4を足し算した結果を表示しただけです。
このような場合の戻り値は「参照型（Range）」になりません。戻り値は「実数型（Double）」になります。
つまり、このような数式が記載されている場合は、Set rng = Evaluate("B1+C1")なんてのは実行時エラーになります。
この仕様は数式を組み立てる時に、大変重要なことなので覚えておいてください。
本格的な計算や関数の使用
Sub Test1_1_4() '(1+2)*(4+5)' Debug.Print Evaluate("(B1+B2)*(C1+C2)") 'SUM(1+2+3)' Debug.Print Evaluate("[SUM(B1:B3)]") End Sub
結果
27 6
このように括弧で優先順位を変えたり、ワークシート関数を組み込んだりもできます。
以上のように、Excelワークシート上で書くような数式をVBA上で計算させることが可能なのがEvaluateです。
配列の返却
Sub Test1_1_5() Dim arr arr = Evaluate("A1:C3").Value '※Join2:二次元配列をTSV形式で連結する独自関数 Debug.Print Join2(arr) End Sub
結果
a1 1 4 a2 2 5 a3 3 6
セル範囲を指定した場合は、セル範囲への参照を示すRangeオブジェクトが返ります。
今回は.Valueで二次元配列の値を取得しarrに格納しました。
それをテスト用に単一の文字列に繋いで出力してみました。
つまり、複数セルになっても単一セルのEvaluateとRangeの関係と同じですね。
Evaluateと角括弧の違い
皆さんが絶対に疑問に思うのがEvaluate()と[]の違いでしょう。
ここまで紹介したように、出来ることはどちらの記法でも同じです。
しかし[]角括弧を使った記法には、いくつかの制限があります。
VBAの変数・関数が使えない
角括弧に挟まれたコードは、ワークシート上の数式と同じようにVBAの変数は使うことができません。
ただし、Evaluate("文字列")なら、文字列合成により変数を数式に組み込む事が可能です。
Sub Test1_2_1() Dim n As Long: n = 2 Dim m As Long: m = 10 '式の解釈：=2+10 Debug.Print Evaluate(n & "+" & m) '式の解釈：=n & "+" & m Debug.Print [n & "+" & m] End Sub
結果
12 エラー 2029
角括弧の方は、要するにこう書いてるのと同じことをしようとしています。
Debug.Print Evaluate("n & ""+"" & m")
仮にnとmが名前定義してあった場合、動いてしまうかもしれませんね。
ただしEvaluate()も万能ではなくて、配列は数式に組み込むことが出来ません。配列は文字列化できませんからね。
（Joinで文字列化して渡した後、数式で分解するという荒業もありますが・・・可読性は最悪です）
VBAに式がチェックされない
角括弧の中では何をどのように書いても叱られません。
実際に実行して結果がエラー値となることで初めて式に問題があった事がわかります。
したがって、Evaluateで入れ子の式を書くとデバッグに大変苦労します。
また、VBEのお節介で半角スペースが2回続けて記載しても消されることがありません。
残念ながら「変数名と一致した名前は大文字小文字が変化する」という仕様は引き継いでいるようですが、区別せず実行されるので困ることはないでしょう。
（Adodbでも列指定で角括弧が登場しますが、そちらでは大文字小文字の変化が邪魔になるそうです）
ステートメントの折返しができない
先の通り[～]は何を書いても式のチェック対象外なのですが、例外があります。それは「改行」です。
したがって、[ ]角括弧の中では_ 改行によるステートメントの折返しが出来ません。
ただし、そもそもEvaluateに使える式の文字数は255文字までという制限があるので、どちらの記法だろうと特に複雑な数式は書けません。
また、VBEは24回しか折返し出来ないので、アンダースコアに拘らずに文字列連結しちゃったほうが良さそうですね。
Evaluateとシート上で書いた数式の仕様の違い
シート上の数式で配列数式として計算させる時は、Ctrl+Shift+Enterにより、Evaluateでは必要ありません。全て自動的に配列数式として計算され、変数には配列のまま格納されます。{}波括弧で囲いますが
※2019/12頃：スピル導入に伴いワークシート上の数式も配列数式が既定となりましたので、Evaluateとの差異はなくなりました。
たとえば、
Arr = [TRANSPOSE(ROW(1:5))]
は
1;2;3;4;5
になります。
それどころか、
Arr = [{ROW(1:5)}]
は
エラー 2015
になります。
一方で定数は（数字、文字列問わず）
Arr = [{1,2,3,4,5}]
のように波括弧が必要になります。
セミコロンとコンマの違い
よく読んでいる人は、セミコロンとカンマが使い分けられている事に気がついた人がいるかも知れないので説明しておきます。
Excelの数式に現れるセミコロン;とコンマ,は、どちらも配列の区切りを示すものですが若干意味が異なります。
例えば、
Sub Test2_2() Dim Arr: Arr = [{1,2,3,4,5}] Debug.Print Join(Arr, ",") End Sub
は
| 式 | 式 | 値 | 型 |
|---|---|---|---|
| Arr | Variant/Variant(1 to 5) | ||
| ├ | Arr(1) | 1 | Variant/Double |
| ├ | Arr(2) | 2 | Variant/Double |
| ├ | Arr(3) | 3 | Variant/Double |
| ├ | Arr(4) | 4 | Variant/Double |
| └ | Arr(5) | 5 | Variant/Double |
だが、
Arr = [{1;2;3;4;5}]
Arr = [ROW(1:5)]
Arr = [COLUMN(A:E)]
は
| 式 | 式 | 式 | 値 | 型 |
|---|---|---|---|---|
| Arr | Variant/Variant(1 to 5, 1 to 1) | |||
| ├ | Arr(1) | Variant(1 to 1) | ||
| │ | └ | Arr(1,1) | 1 | Variant/Double |
| ├ | Arr(2) | Variant(1 to 1) | ||
| │ | └ | Arr(2,1) | 1 | Variant/Double |
| ├ | Arr(3) | Variant(1 to 1) | ||
| │ | └ | Arr(3,1) | 1 | Variant/Double |
| ├ | Arr(4) | Variant(1 to 1) | ||
| │ | └ | Arr(4,1) | 1 | Variant/Double |
| └ | Arr(5) | Variant(1 to 1) | ||
| └ | Arr(5,1) | 1 | Variant/Double |
になります。
この対策は簡単で、
Arr = [TRANSPOSE({1;2;3;4;5})]
とすれば良いのです。
詳しくは下記の記事でお勉強してください。
Evaluateの活用法
解説が長くなりましたが、ここからは活用方法を紹介していきます。
高精度時刻の取得
Sub AppWait() Dim nStart As Double, nStop As Double: nStart = Now() Dim fStart As Double, fStop As Double: fStart = [Now()] Dim tStart As Double, tStop As Double: tStart = Timer Application.Wait [Now() + "00:00:01"] Application.Wait [Now() + "00:00:00.1"] Application.Wait [Now() + "00:00:00.01"] Application.Wait [Now() + "00:00:00.001"] nStop = Now(): Debug.Print Format$((nStop - nStart) * 24 * 3600, "0.0000") & " sec." fStop = [Now()]: Debug.Print Format$((fStop - fStart) * 24 * 3600, "0.0000") & " sec." tStop = Timer: Debug.Print Format$(tStop - tStart, "0.0000") & " sec." End Sub
配列の作成
Evaluateを使えば配列を生み出す事ができます。
ワークシート上の数式と同じで、配列定数の作成は波括弧です。
Sub Arr1Create() Dim arr: arr = [{1,5,6,4,7,4}] Debug.Print Join(arr, ",") '1,5,6,4,7,4 End Sub
単なるリテラルによる一次元配列の生成ならArray()やVBA.Array()でも作れますから、そこまで需要は無いかもしれません。
実はEvaluateなら二次元配列も生み出す事ができます。
Sub Arr2Create() Dim Arr: Arr = [{1,3,6;4,2,5;6,8,9}] Debug.Print Join2(Arr, ",", vbLf) '1,3,6 '4,2,5 '6,8,9 End Sub
もちろん文字列でもOK
Sub Arr2CreateStr() Dim Arr: Arr = [{"あ","い","う","え","お";"か","き","く","け","こ";"さ","し","す","せ","そ"}] Debug.Print Join2(Arr, ",", vbLf) 'あ,い,う,え,お 'か,き,く,け,こ 'さ,し,す,せ,そ End Sub
半角スペース続きが消されないので、ジャグ配列作成で使うと要素の位置が揃って便利です。
Sub CreateJagArr() Dim jag: jag = Array( _ [{"あいうえお", "かきくけこ"}], _ [{"わおん", "やゆよ" }], _ [{"い", "ううう" }]) Debug.Print JoinAny(jag, ",", False) '{{あいうえおかきくけこ},{わおんやゆよ},{いううう}} End Sub
ROW関数を使って連番を生み出す事もできます。
Sub Arr1CreateEx_ROW() Dim arr: arr = [TRANSPOSE(ROW(1:5))] Debug.Print JoinAny(arr, ",") '{1,2,3,4,5} End Sub
※2020/2のOffice365アップデートで、専用のSEQUENCE関数が実装されました。
Sub Arr1CreateEx_Sequence() Dim arr arr = WorksheetFunction.Sequence(5) Debug.Print JoinAny(arr, ",") '{1,2,3,4,5} arr = [SEQUENCE(5)] Debug.Print JoinAny(arr, ",") '{1,2,3,4,5} End Sub
以前1から100の偶数の和（2550）を求めるのにも使いました。
?Evaluate(Join(Filter([TRANSPOSE(ROW(A1:A100))/2], ".", False), "+")) * 2 2550 ?[SUMPRODUCT(ROW(A1:A100)*ISEVEN(ROW(A1:A100)))] 2550 ?Evaluate("MMULT(MOD(COLUMN(B1:CW1),2),ROW(A1:A100))")(1) 2550
よくハマるミス
既に説明済みですが、ハマりやすいことをまとめておきました。
角括弧でVBAは使えない
先の通り、角括弧の内側ではExcel固有のものとなります。
VBAでは不適切な記述を行うとリアルタイムで補正してくれる機能がついているが、角括弧の中では働かないのである。
連続するスペース
v = [A1 + B1] 'vにA1+B1の結果が代入される
閉じていないダブルクォーテーション
v = [A1 + "B1] 'vにエラー 2015が代入される
足りない括弧
v = [(A1+B1] 'vにエラー 2015が代入される
引数に配列は使えない
文字列式で指定しなければならないので、Evaluate()を使っても配列は渡せません。
戻り値には参照型と値型がある
Debug.Print Evaluate("1+2+3")
は値型（Variant/Long）で
Debug.Print Evaluate("A1")
は参照型（Range）なので、気をつけなければなりません。
特にVariant変数にSetを付けずにEvaluateの結果を代入した場合などは要注意です。
実はEvaluateの戻り値に参照型と値型があるというのは非常に重要で、これを分かっていないと、WorksheetFunction.SumIf等の関数を使う際にハマります。
WorksheetFunction.SumIf WorksheetFunction.CountIf
などの第一引数はRange型しか指定できませんのでご注意ください。
これはVBAに限った話ではなくて、ワークシート上の数式でも同じです。
まとめ
実は、本記事の執筆を初めて、投稿できないまま半年間寝かされていましたが、ようやく投稿できました。
その間にスピルが導入されたり、SEQUENCE関数が導入されたり、便利なFILER関数が登場したりして、状況が変わってしまいました。それでもEvaluateの強さは健在です。
うまく使って素敵なVBAを書きましょう。
以上
何か御座いましたらコメント欄、またはTwitterからどうぞ♪
それではまた来週♪ ちゅんちゅん(・8・)
