シートに配置したフォームボタンを使って、ある範囲のセルの内容を別シートのセルの範囲にコピーする。
これだけなんだが、ボタンをクリックすると、カーソルが青いリングに変わって、何やら裏でやっているらしくて、なかなか戻ってこない。よく見ると下のステータスバーに「再計算・・・」というようなメッセージが、何度も現れては消えている。
これは?コピー先を参照しているセルはないはずだが?
原因調査
というわけで、どういう場合にcalculateイベントが発生しているか、ちょっと調査してみた。
1.まず、VBAのコード部分について、
セルの内容をコピーするというコードだったので、セル操作に関するコードを色々とためしてみた結果、
A.セルの内容を変更するようなコードでイベント発生
Range("A1").Value = 1
といったような操作でイベント発生。一方、幅や色を変更といった操作では、イベントは発生しなかった。
B.対象となるシートにはよらない
ことが判明。例えば、シート1、2、3とあり、内容が変更になるシートが”シート3”だったとしても、”シート1”、”シート2”でも、calculateイベントが発生する。
ただし、A、Bとも、後述する特定の種類の数式がそのシートに記述されていることが条件。
2.数式部分について。
再計算が発生しているので、どこかの数式が悪さをしているのだろう。ということで、数式を色々と試してみた。その結果、calclateイベントが発生したのは、
日付/時刻関連 NOW、TODAY
検索/行列関連 OFFSET、INDIRECT
情報関連 CELL、INFO
上記の関数を使用した場合、イベントが発生した。よく使うIF、SUM、INDEX、MATCHといった関数は問題ない。
(ただし、全部の関数は試していないので、これ以外にもあるかも)
すなわち、上述したように、これらの数式が記述されているシート内のセルに対して、その内容を書き換えるような処理が行われた場合、calclateイベントが発生する。(実際に問題になったエクセルではINDIRECT関数を使用していた。)
解決策
さて、いろいろと解ってきたが、なぜ、こうなるかは不明のまま。
ちょっと解りそうにない。
とは言っても、なんとかしなくては使い物にならない。
上記の関数を使用しないというのも、1つの手ではあるが、そうもいかない。
そこで思い出したのが、エクセル自体の設定で、再計算を自動かマニュアルに変更できることだ。
イベント発生をコントロールする”Application.EnableEvents”はよく使うが、再計算についても同様の処理ができるのでは?
ということで調べた結果、やはりあった。
Application.Calculation = xlCalculationManual
このコードを、セルの内容を書き換える処理の前に入れてやれば良い。
そうすると、calclateイベントは発生しなくなる。
終わったら、
Application.Calculation = xlCalculationAutomatic
として自動に戻しておくことも忘れずに。
根本原因は不明なままなので、ちょっと不完全燃焼だが、しょうがない。
今回はこのくらいにしておこう。
0 件のコメント:
コメントを投稿