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 の意味不明な挙動で悩みましたが、おかげさまで解決できました。
    ありがとうございます。

コメントを残す

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

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください