エクセルVBAのChangeイベント時の再計算

質問

掲題の件ですが、VBAにて、
あるセルの値を変更したときに処理を行うChangeイベントについて困っております。

処理速度向上の為、この処理を行う際、再計算の処理を行わないようにしたいのですが、

Application.Calculation = xlCalculationManual

を処理の頭に入れても、

Changeイベント発生→再計算処理→再計算を手動にする処理→本処理

となってしまうため、上手くいきません。
何かいい手立てはないものでしょうか。

エクセル2010です。

回答No.3

angel_Z · ベストアンサー

こんにちは。

これもだめですか？
Changeイベントの最初に

If ActiveSheet.Name <> Me.Name Then Exit Sub

お礼

たびたびのご回答有難うございます。

試してみましたが、やはりうまくいかないようです。
あきらめて、ボタンを設置しようと思います。

有難うございました。

回答No.2

DreamyCat

そうですか？　不思議ですね。

　短いコードなら提示するといいでしょうが
何をどうしているのか読む側にはわからないので
VBAコード以外に、一般機能で使っている関数も書いてみるといいかもしれません。

あと、Worksheet_SelectionChangeイベントだとしたら、
多量のデータの書き換えなどのためには使わないほうがいいかと。
CommandButtonクリックで開始するのでは都合が悪いのですか？

補足

ご回答ありがとうございます。

今回の処理内容を簡単に説明に説明すると、
複数のセルに入っている値を空白セルと重複セルを除外して、
全てつなぎ合わせて別セルに表示するといった内容です。
CONCATENATEや＆でつなぐにはセル数が多すぎまるため、
VBAで処理をしています。

そのほか、
VLOOKUPで他シートからデータを引っ張るというセルが7000個ほどあります。
再計算に時間がかかっているのはおそらくこれが原因だと思っています。

ただ、今回の処理で再計算が自動でかからなくて困るということは無いので、
今回の処理をするときだけ再計算をしないようにしたかったのです。
ちなみにWorksheet_SelectionChangeではなくWorksheet_Changeです。

CommandButtonも考えましたが、あくまで最終手段ということで、
なるべくChangeイベント時に処理したいと思っています。

回答No.1

angel_Z

こんにちは。

イベント無効の
Application.EnableEvents = False
を入れてみては。

参考URL
http://vba.ti-da.net/e2482933.html
http://www.eurus.dti.ne.jp/yoneyama/Excel/vba/vba_event.html

デバック、戻し忘れの回避策
http://d.hatena.ne.jp/tiri_tomato/20110910/1315623626

Changeイベントで
with Application
.EnableEvents = False 'イベント無効
.Calculation = xlCalculationManual　'←再計算の設定を手動
.ScreenUpdating = False　'←画面の再描画をOFFにする
end with

～処理～

with Application
.Calculation = xlCalculationAutomatic　'←再計算の設定を自動
.ScreenUpdating = True　'←画面の再描画をONにする
.EnableEvents = True'イベント有効
end with

補足

ご回答ありがとうございます。

Application.EnableEventsも試してみてはいました。
しかしながら、処理の一番頭にブレークポイントを設定して試してみたところ、

セルの値を変更（Worksheet_Changeイベント発生）
↓
再計算がかかる。
↓
Worksheet_Change内の処理が始まる。
（Application.EnableEvents = falseやApplication.Calculation = xlCalculationManualが走る）

なので、どうにもこうにもいかない状態です。

セルの値を変更するときは、あらかじめ再計算を手動にしておけばいいんでしょうが、
いちいちオプションや別のボタンを押して手動にしておくというのも、
あまり実用的ではないかなと思いまして・・・。

やはりCommandButtonをトリガーにするしかないのでしょうか。