ExcelのVBAで、標準モジュールにワークシートで使うユーザー関数を記述しています。
ユーザー関数は、使い勝手を組み込みのワークシート関数と同様にするため、 Application.Volatile (True) で自動再計算するようにしており、数百カ所以上のセルに使用しています。
しかし、他のVBAモジュールでユーザー関数の戻り値が変わるような操作をすると、その都度
数百か所以上が再計算されるため、非常に時間がかかります。
ユーザー関数のApplication.Volatile (False) にすると瞬間で終了する処理が、数分かかる場合も
あります。
他のVBAモジュールから、一時的にユーザー関数の処理内容を変更するようなことは可能でしょうか。
No.1ベストアンサー
- 回答日時:
ご質問の直接の回答としては
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
- 回答日時:
再掲:
>引数として与えたセルに変更を加えた(=再計算チェーンに載ってくる)場合のみ適切に再計算され
引数として与えたセルに変更を加えて無ければ、再計算チェーンに載らず放置されます。引数とせずコードの中で独自にどこかのセルの値を計算に使ってるだけでは、もちろん引数にはなりません。
どの値(変数)が変更された場合に再計算が必要なのか分析して、適切に引数として設計してください。
たびたびありがとうございます。
よく考えてみれば、ユーザー定義関数内で参照しているセルを変更しても、引数を変更しなければ関数が起動するはずはありませんね。
Application.Volatile (True) がなければ再計算しない理由がわかりました。
No.2
- 回答日時:
こんばんは。
> Application.Volatile (True)
基本的には、Volatile で、不揮発性を変更しても、無意味です。引数を入力の際に、再計算イベントが走りますから、多くを使う場合は不要です。要するに、入力するたびに、一斉に、再計算イベントが走ってしまって、処理が遅くなっているものだと思います。
実際、ワークシート関数で作れないものは、特殊な場合を除き、ほとんどありません。
>数百カ所以上のセルに使用しています。
ふつうは、VBAのユーザー定義関数を多用するのでしたら、プロシージャで処理したほうがよいです。
>他のVBAモジュールから、一時的にユーザー関数の処理内容を変更するようなことは可能でしょうか。
意味がよく分かりませんが、VBAでコードの内容を変えるという意味でしょうか。
もしそうなら、ユーザー定義関数の処理内容を変更することは、裏ワザとしてはあったとしても、VBAプログラマーとしては、それは禁じ手だと思います。
ありがとうございます。
> VBAでコードの内容を変えるという意味でしょうか。
その通りです。
30年以上前、Z80やMC6809のマシン語で処理速度を高めるため、処理内容に応じて自分自身のオペランドどころか、オペコードも書き換えるという裏技をよく使っていました。
当時から「禁じ手」であることは認識していましたが、ひょっとしたらVBAでもできるかもしれない、と思いました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) Excel のユーザー定義関数でソルバーが動作しない 1 2022/09/05 19:51
- Excel(エクセル) Application.Volatile利用(excel2003) 1 2023/02/06 10:11
- Excel(エクセル) ユーザー関数の自動計算(excel2003) 1 2023/02/06 06:46
- Visual Basic(VBA) ExcelのVBAを使い、複数シートの同一箇所を、同一条件にて一括でソルバーを回す方法について 1 2022/04/23 11:49
- Visual Basic(VBA) vba メモリ節約 3 2022/09/16 21:45
- Excel(エクセル) エクセル・スプレッドシートで、一定数を超えたらゼロから再累計する方法 8 2022/05/28 03:52
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
- Excel(エクセル) エクセル バーコード作成で他のシートを参照するには? 2 2023/05/03 16:57
- Visual Basic(VBA) マクロについて教えてください。 4 2023/06/06 09:06
- Visual Basic(VBA) この関数と同じ処理をVBAで行うにはどうしたら良いでしょうか? これは、1列の中に同じ値が複数存在し 21 2022/07/07 07:48
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
ヘッダー
-
エクセルで、時間の計算をした...
-
IF関数で、時間を条件にしたい...
-
Excel 関数? 文字列に...
-
エクセルの計算結果が0になって...
-
数式の計算結果により表示され...
-
エクセル 1万時間を越える際の...
-
エクセルの使い方 1日=7時間45...
-
エクセル 月数を0.5ヶ月単位で...
-
エクセルでの時間計算(2時間30...
-
EXCELの関数に関する質問です
-
TEXTCALCの使い方
-
Excelの計算式内の文字列の一括...
-
エクセルで比率を計算
-
エクセルで 足し算や割り算の...
-
計算結果が「0」のセルだけを「...
-
エクセル・計算式をテキストデ...
-
エクセルで、分数の分子分母を...
-
エクセルでDeleteキーを...
-
IF関数で出した数値をSUM関数で...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
IF関数で、時間を条件にしたい...
-
ヘッダー
-
エクセルの使い方 1日=7時間45...
-
エクセルで、時間の計算をした...
-
EXCELの関数に関する質問です
-
エクセルで 足し算や割り算の...
-
Excelの計算式内の文字列の一括...
-
数式の計算結果により表示され...
-
エクセルでの時間計算(2時間30...
-
TEXTCALCの使い方
-
計算結果が「0」のセルだけを「...
-
エクセル・計算式をテキストデ...
-
エクセル 1万時間を越える際の...
-
エクセルの計算結果が0になって...
-
excel 参照しているセルに色が...
-
エクセルで足し算と掛算を混ぜ...
-
AかBに入力があった時Cに反映、...
-
Excel 関数? 文字列に...
-
エクセル 月数を0.5ヶ月単位で...
-
エクセルで比率を計算
おすすめ情報