Worksheet_Changeが繰り返し動く理由を調べる

LWP | Worksheet_Changeが繰り返し動く理由を調べる

Worksheet_Changeが繰り返し動く理由を調べる

図解1|一度の入力から、イベントへ戻る

A2の変更からChangeイベントが動き、内部のB2書込みで再び同じイベントへ入る模式図

図1|利用者の一回の操作と、イベントの呼出し回数を分ける。

解説1|一度の入力から、イベントへ戻る

ストーリー

コイ丸先輩「一回しか入力していないのに、また呼ばれるのか。」

シャケモン「処理の中のセル書込みも、きっかけになります。」

Worksheet_Changeは、シートのセルが変更されたときに呼ばれるイベント手続きである。その手続きが別のセルへ値を書けば、イベントが有効な場合は、その変更をきっかけに同じ手続きへ再び入ることがある。利用者が二回入力したという意味ではない。

今回の練習用シート

セル

役割

数値6を入れた例

A2

利用者が変更する入力値

6

B2

入力値を2倍した結果

12

業務の受注処理全体を作る記事ではなく、セル変更と呼出しの関係を調べる最小例である。実際の事故の記録ではない。既存業務ブックではなく、新しい検証用ブックとシートを使う。

読むポイント

再計算で数式の結果だけが変わる場合は、このChangeイベントとは別に考える。今回の対象は、A2への入力と、コードによるB2への値の書込みである。

図解2|セル書込みが、次の呼出しを作る

TargetがA2の一回目とB2の二回目を分け、二回目は対象外として戻る関係を示す図

図2|対象外として終了しても、入口へ来たことは変わらない。

解説2|セル書込みが、次の呼出しを作る

ストーリー

コイ丸先輩「B2への書込みでも、同じ入口へ戻るんだね。」

Targetは、今回変更された範囲を表す。A2の入力で呼ばれたときはA2、B2への書込みをきっかけに呼ばれたときはB2になる。コードを読むときは、呼出しごとにTargetを区別する。

原因を確かめる短い例

VBEのプロジェクトエクスプローラーで検証用シートを開き、そのシートのコードモジュールへ置く。標準モジュールには置かない。

01

Private Sub Worksheet_Change(ByVal Target As Range)

02

    Debug.Print "entry", Target.Address

03

    If Intersect(Target, Me.Range("A2")) Is Nothing Then Exit Sub

04

    If Target.CountLarge <> 1 Then Exit Sub

05

    If Not IsNumeric(Target.Value2) Then Exit Sub

06

    Me.Range("B2").Value2 = CDbl(Target.Value2) * 2

07

End Sub

A2へ6を直接入力すると、イミディエイトウィンドウでA2とB2の二つの入口を確認できる。B2側では対象範囲の条件によって終了するので、この例は二回の呼出しで戻る。同じセルへ無制限に書き戻す再帰を実行する必要はない。

読むポイント

Meはこのイベントを持つシートを表す。TargetとMe.Range("A2")の関係を先に決め、別シートや選択状態を使って対象を推測しない。

図解3|対象セルと入力の大きさを絞る

A2との交差とTargetのセル数を順に調べ、複数セル入力は処理しない条件を示す図

図3|処理する範囲を決めることと、イベントを止めることは別である。

解説3|対象セルと入力の大きさを絞る

ストーリー

シャケモン「対象の範囲と、複数セル入力の扱いを決めます。」

セル変更は、一つのセルへの入力だけとは限らない。複数セルの貼付けでも呼ばれるため、Target.Value2を一つの数値として扱う前に、今回どの大きさの入力を受け付けるかを決める。

今回の対象

変更範囲

この最小例の動作

A2だけ

数値を調べ、B2を更新する

B2だけ

対象外として終了

A2:A3をまとめて貼付け

複数セルなので終了

A2と交差しない別の範囲

対象外として終了

Intersectは二つの範囲の重なりを調べる。重なりがなければNothingになる。CountLargeで変更セル数が1かを確認した後に、単一の値を調べる。順序を分けると、どの前提を確認したかを追いやすい。

読むポイント

複数セルの変更を処理せずに手続きを終了するのは、この最小例での方針である。利用者がまとめて貼り付ける業務では、行ごとに処理するなど別の設計が要る。入力を見送ったのに更新済みと表示する処理を付けない。

図解4|内部の書込み中だけイベントを止める

EnableEventsの開始値を保存し、内部書込み中だけFalseにして、開始値へ戻す流れを示す図

図4|復元先はTrueという固定値ではなく、処理前に保存した値である。

解説4|内部の書込み中だけイベントを止める

ストーリー

コイ丸先輩「止めたままにすると、次の入力も動かないな。」

シャケモン「開始時の値を保存し、最後に戻します。」

Application.EnableEventsをFalseにすると、同じExcelインスタンスのイベントを無効にできる。今回の内部書込みによる再入を避ける方法になるが、一枚のシートだけに閉じた設定ではない。開始時の値を保存し、正常時も失敗時も戻す必要がある。

変更する状態

段階

設定の扱い

開始前

x_eventsへ現在値を保存

内部の書込み中

EnableEventsをFalseにする

終了前

EnableEventsをx_eventsへ戻す

実行中フラグで入口を抜ける方法は、イベントの発生そのものを止める方法とは違う。フラグ方式では呼ばれてから処理を見送る。どちらを使うかは、処理範囲と再入をどう扱うかで決める。

読むポイント

別ブックのイベントにも影響するため、既存のExcelで無造作に試さない。図5のコードは専用ブックで確認し、マクロの途中停止やVBEのリセットも設定復元を妨げ得ることを理解して使う。

図解5|失敗した場合も、同じ後始末へ

正常経路とエラー経路を同じCleanUpへ合流させ、保存したイベント設定を戻す図

図5|ラベルへ処理をつなぐコードがあって初めて後始末が行われる。

解説5|失敗した場合も、同じ後始末へ

今回のコード

図2のイベント手続きを削除し、同じ検証用シートのコードモジュールへ次の一つだけを置く。

01

Private Sub Worksheet_Change(ByVal Target As Range)

02

    If Intersect(Target, Me.Range("A2")) Is Nothing Then Exit Sub

03

    If Target.CountLarge <> 1 Then Exit Sub

04

    Dim x_events As Boolean, x_number As Long

05

    Dim x_description As String

06

    x_events = Application.EnableEvents

07

    On Error GoTo Failed

08

    Application.EnableEvents = False

09

    If Not IsNumeric(Target.Value2) Then Err.Raise 13

10

    Me.Range("B2").Value2 = CDbl(Target.Value2) * 2

11

CleanUp:

12

    Application.EnableEvents = x_events

13

    If x_number <> 0 Then Debug.Print "failed", x_number, x_description

14

    Exit Sub

15

Failed:

16

    x_number = Err.Number

17

    x_description = Err.Description

18

    Resume CleanUp

19

End Sub

読むポイント

Failedで番号と説明を退避してからResume CleanUpで合流する。CleanUpとFailedはラベル名であり、自動的に実行される予約語ではない。正常時も書込みの後にCleanUpへ進む。失敗時はB2が更新されないため、前の数字を今回の結果として使わない。

図解6|次の入力でも、動くことを確かめる

数値6、文字の未定、次の数値10を順に入力し、結果と設定復元を確かめる図

図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。