Evaluate を使うと2回実行されてしまう(Excel VBA)


Excel VBA には Evaluate というメソッドが存在しています。ざっくりいうと、セルにいれるような数式の実行結果を返してくれるものです。SUM のような標準で用意されているワークシート関数はもちろんですが、数式には UDF(ユーザー定義関数。マクロの Function とか Sub とか)を入れることも可能です。しかし、なぜか Evaluate に UDF を渡すと2回呼び出されてしまいます。

結論から書くと、ここのサイトに記載があります。長いですが「Evaluate Method limitations」の章を確認してください。

Activesheet.Evaluate(“=0+MyUdf()”)

出典:Controlling Calculation from VBA – Decision Models

Application ではなく、シートの Evaluate を使用し、UDFの前に 0+ を付けるのがポイントです。

いろいろなサイト(こことかこことか)に記述がありますがバグのように見受けられます。長いこと、この挙動のままだったようなので、今更直すと既存のコードに不具合が出そうで怖いですね。

検証

Application とシートの Evaluate メソッド(とついでに [] も)を呼び出し、イミディエイトに文字列を出力するコードを書いてみます。

Option Explicit

Sub 実行()
    Application.Evaluate ("関数(""Application"")")
    ActiveSheet.Evaluate ("関数(""ActiveSheet"")")
    [関数("[]")]
    
    Application.Evaluate ("0+関数(""Application 0+"")")
    ActiveSheet.Evaluate ("0+関数(""ActiveSheet 0+"")")
    [0+関数("[] 0+")]
End Sub

Sub 関数(ByVal caseName As String)
    Debug.Print (caseName)
End Sub

結果は以下のようになりました。ActiveSheet.Evaluate ("0+関数(""ActiveSheet 0+"")") のみ1回だけ実行されています。

補足

単純に SUM を使いたい場合は WorksheetFunction.Sum という方法があります。また、UDF 呼び出したい場合は Evaluate を使わない方がいいと思います。自動補完が使えませんし、Evaluate の場合はエラーが発生しても何も起こらないので気が付きにくいです。Evaluate の使用は避けた方が保守性・可読性が高くなりますので、代替手段がないか探した上で使用を検討しましょう。


“Evaluate を使うと2回実行されてしまう(Excel VBA)” への1件のコメント

  1. Evaluate の意味不明な挙動で悩みましたが、おかげさまで解決できました。
    ありがとうございます。

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

This site uses Akismet to reduce spam. Learn how your comment data is processed.