【Excel VBA】Worksheet_Changeが繰り返し実行される原因と対処法｜無限ループを防ぐ方法

みなさんこんにちは。

Excel VBAでWorksheet_Changeを使用していると、セルを変更しただけなのに処理が何度も実行され、困った経験をしたことがある方も多いかと思います。

今回は、イベントが繰り返し発生する原因と、EnableEventsで無限ループを防ぐ方法についてご紹介したいと思います。

Worksheet_Changeが繰り返される原因

Worksheet_Changeは、ワークシートのセルが変更されたときに実行されるイベントです。VBAからセルの値を書き換えた場合にも発生します。

例えばイベント内でTarget.Value = UCase$(Target.Value)と書くと、入力されたセルを大文字に変更します。しかし、その書き換え自体が再びイベントを発生させるため、処理を繰り返す原因となります。

そこで、セルを更新する間だけApplication.EnableEvents = Falseにして、イベントを一時的に停止します。

動作確認用のExcelを準備する

新しいExcelブックのSheet1を使用します。A1に「入力文字列」、A2に「excel」と入力してください。最初は小文字のままで大丈夫です。

EnableEvents=Falseで再実行を防ぐコード

VBAエディターを開き、標準モジュールではなくSheet1のシートモジュールに次のコードを貼り付けてください。

Private Sub Worksheet_Change(ByVal Target As Range)
    ' A2～A10の1セルが変更された場合だけ処理する
    If Target.CountLarge <> 1 Then Exit Sub
    If Intersect(Target, Me.Range("A2:A10")) Is Nothing Then Exit Sub

    ' エラーが発生しても復旧処理へ移動する
    On Error GoTo ErrHandler

    ' セルの更新によるイベントの再発火を防ぐ
    Application.EnableEvents = False

    ' 入力された英字を大文字に変換する
    Target.Value = UCase$(CStr(Target.Value))

ExitHandler:
    ' 正常終了・エラー時ともにイベントを有効に戻す
    Application.EnableEvents = True
    Exit Sub

ErrHandler:
    MsgBox "処理中にエラーが発生しました：" & Err.Description, vbExclamation
    Resume ExitHandler
End Sub

Intersectで対象範囲をA2～A10に絞り、複数セルの一括変更は対象外にしています。EnableEvents=Falseの間に同じセルを書き換えるため、イベントの再発火を防げます。

エラーが発生してもTrueへ戻す

イベントを停止したまま処理が終わると、その後のWorksheet_Changeも動かなくなってしまいます。

このコードではOn Error GoTo ErrHandlerを使用し、エラー時もResume ExitHandlerで復旧処理へ移動します。正常終了とエラー終了の両方でTrueに戻すことがポイントとなります。

実行結果を確認する

コードを保存したら、Sheet1のA2へ「excel」を入力し直してください。「EXCEL」に変われば成功です。A3に「vba」と入力すると「VBA」に変わります。F5で実行するのではなく、セルへの入力で動作を確認してください。

イベントが動かない場合は、VBAのイミディエイトウィンドウ（Ctrl+G）で? Application.EnableEventsを実行し、Falseになっていないか確認してください。Falseの場合はApplication.EnableEvents = Trueで復旧できます。

なお、VBAの「リセット」や強制終了ではエラー処理を通らない場合があるので注意してください。また、今回は複数セルの貼り付けを処理しないサンプルとなります。

EnableEventsを利用した処理の高速化については、

でもご紹介しています。

最後に

いかがでしたでしょうか。今回はWorksheet_Changeの無限ループを防ぐ方法についてご紹介しました。イベントの一時停止と、エラー時の復旧をセットで実装してください。少しでも参考になれば幸いです。

開発依頼について

ココナラでWebスクレイピング開発サービスを出品しております。

自分で開発をしようと思ったけど、VBAでのネット記事が少なく困っている方も多いかと思います。

そんな方はいつでもお気軽にご相談ください。

また、本ブログからご依頼いただいた方については割引特典がございますので、

ご不明点と合わせてメッセージをいただけると幸いです。

Webスクレイピング以外にもWebアプリの開発サービスも出品しております。

こちらについても、本ブログからご依頼いただいた方については割引特典がございますので、

お気軽にご相談ください。

ご興味がある方はこちら！！

@KP16Wt9UOB97870