VBAユーザー関数を外部から制御

質問

ExcelのVBAで、標準モジュールにワークシートで使うユーザー関数を記述しています。
ユーザー関数は、使い勝手を組み込みのワークシート関数と同様にするため、 Application.Volatile (True) で自動再計算するようにしており、数百カ所以上のセルに使用しています。
しかし、他のVBAモジュールでユーザー関数の戻り値が変わるような操作をすると、その都度
数百か所以上が再計算されるため、非常に時間がかかります。
ユーザー関数のApplication.Volatile (False) にすると瞬間で終了する処理が、数分かかる場合も
あります。
他のVBAモジュールから、一時的にユーザー関数の処理内容を変更するようなことは可能でしょうか。

回答No.1

keithin · ベストアンサー

ご質問の直接の回答としては
application.calculation = xlcalculationmanual
で一旦再計算を手動に止めておき、一渡りの処理を終えたら
application.calculation = xlcalculationautomatic
で再計算させます。

ただし。
＞組み込みのワークシート関数と同様にするため、 Application.Volatile (True) で自動再計算するようにしており

ごく一般論でいうと、ワークシートのセルを引数に取るユーザー定義関数で作成しておき、通常はvolatileは制御しません。

例：
public function test(byval target as range) as variant
test = 引数として渡されたセルの値を元に何某の計算を行う
end function

といった具合に工夫して作成することで、引数として与えたセルに変更を加えた（＝再計算チェーンに載ってくる）場合のみ適切に再計算され、シート上の全部の当該関数が毎度毎度一斉に再計算されるみたいな事態は回避します。

お礼

ありがとうございます。
application.calculation による制御で、自動計算による速度低下が回避できました。

> public function test(byval target as range) as variant
ただ、ユーザー定義関数を上記のように変更しても、Application.Volatile (True)を入れなければ再計算しません。
私が作成したユーザー関数は、引数として渡されたセルだけでなく、引数に応じた、引数とは別のセルも参照して結果を返します。
これが関係しているのでしょうか。

回答No.3

keithin

再掲：
＞引数として与えたセルに変更を加えた（＝再計算チェーンに載ってくる）場合のみ適切に再計算され

引数として与えたセルに変更を加えて無ければ、再計算チェーンに載らず放置されます。引数とせずコードの中で独自にどこかのセルの値を計算に使ってるだけでは、もちろん引数にはなりません。

どの値（変数）が変更された場合に再計算が必要なのか分析して、適切に引数として設計してください。

お礼

たびたびありがとうございます。
よく考えてみれば、ユーザー定義関数内で参照しているセルを変更しても、引数を変更しなければ関数が起動するはずはありませんね。
Application.Volatile (True) がなければ再計算しない理由がわかりました。

回答No.2

WindFaller

こんばんは。

> Application.Volatile (True)
基本的には、Volatile で、不揮発性を変更しても、無意味です。引数を入力の際に、再計算イベントが走りますから、多くを使う場合は不要です。要するに、入力するたびに、一斉に、再計算イベントが走ってしまって、処理が遅くなっているものだと思います。

実際、ワークシート関数で作れないものは、特殊な場合を除き、ほとんどありません。

>数百カ所以上のセルに使用しています。
ふつうは、VBAのユーザー定義関数を多用するのでしたら、プロシージャで処理したほうがよいです。

>他のVBAモジュールから、一時的にユーザー関数の処理内容を変更するようなことは可能でしょうか。
意味がよく分かりませんが、VBAでコードの内容を変えるという意味でしょうか。
もしそうなら、ユーザー定義関数の処理内容を変更することは、裏ワザとしてはあったとしても、VBAプログラマーとしては、それは禁じ手だと思います。

お礼

ありがとうございます。
> VBAでコードの内容を変えるという意味でしょうか。
その通りです。
30年以上前、Z80やMC6809のマシン語で処理速度を高めるため、処理内容に応じて自分自身のオペランドどころか、オペコードも書き換えるという裏技をよく使っていました。
当時から「禁じ手」であることは認識していましたが、ひょっとしたらVBAでもできるかもしれない、と思いました。