LWP | Worksheet_Changeが繰り返し動く理由を調べる
Worksheet_Changeが繰り返し動く理由を調べる
図解1|一度の入力から、イベントへ戻る

図1|利用者の一回の操作と、イベントの呼出し回数を分ける。
解説1|一度の入力から、イベントへ戻る
ストーリー
コイ丸先輩「一回しか入力していないのに、また呼ばれるのか。」
シャケモン「処理の中のセル書込みも、きっかけになります。」
Worksheet_Changeは、シートのセルが変更されたときに呼ばれるイベント手続きである。その手続きが別のセルへ値を書けば、イベントが有効な場合は、その変更をきっかけに同じ手続きへ再び入ることがある。利用者が二回入力したという意味ではない。
今回の練習用シート
セル |
役割 |
数値6を入れた例 |
|---|---|---|
A2 |
利用者が変更する入力値 |
6 |
B2 |
入力値を2倍した結果 |
12 |
業務の受注処理全体を作る記事ではなく、セル変更と呼出しの関係を調べる最小例である。実際の事故の記録ではない。既存業務ブックではなく、新しい検証用ブックとシートを使う。
読むポイント
再計算で数式の結果だけが変わる場合は、このChangeイベントとは別に考える。今回の対象は、A2への入力と、コードによるB2への値の書込みである。
図解2|セル書込みが、次の呼出しを作る

図2|対象外として終了しても、入口へ来たことは変わらない。
解説2|セル書込みが、次の呼出しを作る
ストーリー
コイ丸先輩「B2への書込みでも、同じ入口へ戻るんだね。」
Targetは、今回変更された範囲を表す。A2の入力で呼ばれたときはA2、B2への書込みをきっかけに呼ばれたときはB2になる。コードを読むときは、呼出しごとにTargetを区別する。
原因を確かめる短い例
VBEのプロジェクトエクスプローラーで検証用シートを開き、そのシートのコードモジュールへ置く。標準モジュールには置かない。
01 |
|
|---|---|
02 |
|
03 |
|
04 |
|
05 |
|
06 |
|
07 |
|
A2へ6を直接入力すると、イミディエイトウィンドウでA2とB2の二つの入口を確認できる。B2側では対象範囲の条件によって終了するので、この例は二回の呼出しで戻る。同じセルへ無制限に書き戻す再帰を実行する必要はない。
読むポイント
Meはこのイベントを持つシートを表す。TargetとMe.Range("A2")の関係を先に決め、別シートや選択状態を使って対象を推測しない。
図解3|対象セルと入力の大きさを絞る

図3|処理する範囲を決めることと、イベントを止めることは別である。
解説3|対象セルと入力の大きさを絞る
ストーリー
シャケモン「対象の範囲と、複数セル入力の扱いを決めます。」
セル変更は、一つのセルへの入力だけとは限らない。複数セルの貼付けでも呼ばれるため、Target.Value2を一つの数値として扱う前に、今回どの大きさの入力を受け付けるかを決める。
今回の対象
変更範囲 |
この最小例の動作 |
|---|---|
A2だけ |
数値を調べ、B2を更新する |
B2だけ |
対象外として終了 |
A2:A3をまとめて貼付け |
複数セルなので終了 |
A2と交差しない別の範囲 |
対象外として終了 |
Intersectは二つの範囲の重なりを調べる。重なりがなければNothingになる。CountLargeで変更セル数が1かを確認した後に、単一の値を調べる。順序を分けると、どの前提を確認したかを追いやすい。
読むポイント
複数セルの変更を処理せずに手続きを終了するのは、この最小例での方針である。利用者がまとめて貼り付ける業務では、行ごとに処理するなど別の設計が要る。入力を見送ったのに更新済みと表示する処理を付けない。
図解4|内部の書込み中だけイベントを止める

図4|復元先はTrueという固定値ではなく、処理前に保存した値である。
解説4|内部の書込み中だけイベントを止める
ストーリー
コイ丸先輩「止めたままにすると、次の入力も動かないな。」
シャケモン「開始時の値を保存し、最後に戻します。」
Application.EnableEventsをFalseにすると、同じExcelインスタンスのイベントを無効にできる。今回の内部書込みによる再入を避ける方法になるが、一枚のシートだけに閉じた設定ではない。開始時の値を保存し、正常時も失敗時も戻す必要がある。
変更する状態
段階 |
設定の扱い |
|---|---|
開始前 |
x_eventsへ現在値を保存 |
内部の書込み中 |
EnableEventsをFalseにする |
終了前 |
EnableEventsをx_eventsへ戻す |
実行中フラグで入口を抜ける方法は、イベントの発生そのものを止める方法とは違う。フラグ方式では呼ばれてから処理を見送る。どちらを使うかは、処理範囲と再入をどう扱うかで決める。
読むポイント
別ブックのイベントにも影響するため、既存のExcelで無造作に試さない。図5のコードは専用ブックで確認し、マクロの途中停止やVBEのリセットも設定復元を妨げ得ることを理解して使う。
図解5|失敗した場合も、同じ後始末へ

図5|ラベルへ処理をつなぐコードがあって初めて後始末が行われる。
解説5|失敗した場合も、同じ後始末へ
今回のコード
図2のイベント手続きを削除し、同じ検証用シートのコードモジュールへ次の一つだけを置く。
01 |
|
|---|---|
02 |
|
03 |
|
04 |
|
05 |
|
06 |
|
07 |
|
08 |
|
09 |
|
10 |
|
11 |
|
12 |
|
13 |
|
14 |
|
15 |
|
16 |
|
17 |
|
18 |
|
19 |
|
読むポイント
Failedで番号と説明を退避してからResume CleanUpで合流する。CleanUpとFailedはラベル名であり、自動的に実行される予約語ではない。正常時も書込みの後にCleanUpへ進む。失敗時はB2が更新されないため、前の数字を今回の結果として使わない。
図解6|次の入力でも、動くことを確かめる

図6|異常終了の後に次の入力を試し、設定の置き忘れを見つける。
解説6|次の入力でも、動くことを確かめる
ストーリー
コイ丸先輩「一度の成功だけでは、終わりにしないんだね。」
シャケモン「失敗後の次の入力まで確かめましょう。」
EnableEventsがTrueの専用Excelで、A2へ6、未定、10を順に入力する。数値ではB2を更新し、文字ではエラー情報をイミディエイトへ出し、次の数値で再び更新できるかを見る。
結果の比較
A2への入力 |
B2 |
確認すること |
|---|---|---|
6 |
12 |
通常の更新 |
未定 |
更新しない |
エラー13の記録と設定復元 |
10 |
20 |
失敗後もイベントが動く |
まとめと今回の仕掛け
セル変更の発生、処理対象の判定、内部書込みの間のイベント抑止は、それぞれ役割が違う。呼出しの経路を先に描くと、何を止め、何を元へ戻すかが見える。入力の全業務条件や成功状態の表示は別途設計する。この短いコードをそのまま業務完成品とは扱わない。
出典
『デバッキング Excel VBA』V041_261028、5.5.2「イベントの二重実行を確認する」を、独立した2倍計算の例へ再構成。Microsoft Learn Worksheet.Change、Application.EnableEvents。
