【VBA】エラー時も設定を元に戻す｜ScreenUpdating等の後片付け

VBAのエラー時にScreenUpdatingなどを元に戻すコードを示すVBEのコード画面 VBA

VBAを速くするために、Application.ScreenUpdating、Application.Calculation、Application.EnableEventsを切り替えるコードがあります。速度への効果は、以前の記事（【VBA】処理が遅い本当の原因｜シート操作が重くなる仕組み）で実測しました。

ただし、この3つは戻し忘れると厄介です。処理の途中でエラーが起きると、末尾に書いた「元に戻す行」まで届かず、設定が変わったまま残ることがあります。

この記事では、エラーで止まっても設定を元に戻す書き方を紹介します。わざとエラーを起こして、実際に戻るかどうかも確認しました。

こんな方に向けた記事です

  • ScreenUpdating = Falseなどを書いているが、エラー時に戻る形になっているか自信がない方
  • マクロが止まったあと、数式が再計算されない、イベントが動かない、といった状態になった方
  • 他の人に渡すマクロの後片付けを整えたい方

戻らないと何が困るのか

3つはいずれもApplicationオブジェクトの設定です。マクロを動かしたブックだけでなく、Excel全体に影響します。

  • ScreenUpdating：Falseのままだと、画面が更新されません
  • Calculation：手動のままだと、数式が自動で再計算されません
  • EnableEvents：Falseのままだと、Worksheet_Changeなどのイベントプロシージャが動きません

CalculationとEnableEventsは、実機でも確認しました。手動計算にすると、セルの値を変えても数式の結果は更新されず、これは別のブックの数式でも同じでした。自動に戻すと更新されました。EnableEventsをFalseにすると、セルの値を変えても、ワークシートのChangeイベントのプロシージャは呼ばれませんでした。Trueに戻すと呼ばれました。

Microsoft Learnにも、ScreenUpdatingはマクロの終了時にTrueへ戻すよう書かれています。（Application.ScreenUpdating property (Excel)）

素朴な書き方だとエラーで戻らない

処理の前に3つを切り替え、最後に元へ戻す書き方です。一見、問題なさそうです。

Sub NaiveMacro()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False

    ' ここに本処理

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
End Sub

途中で実行時エラーが起きると、後ろの3行は実行されません。わざとエラー9を起こして、マクロを画面から実行しました。エラーダイアログで「終了」を押したあとの設定は、次のとおりです。

画面=True、計算=手動、イベント=False。

ScreenUpdatingは、マクロが終わった時点でTrueに戻っていました。今回の環境での結果です。一方、CalculationとEnableEventsは、変更されたまま残りました。

同じマクロの実行中は、ScreenUpdatingもFalseのままでした。呼び出し元のマクロから見ると、3つとも変更された状態です。

解決コード：保存して変更し、Cleanupで戻す

Sub SafeMacro()
    Dim prevScreen As Boolean
    Dim prevCalc As XlCalculation
    Dim prevEvents As Boolean
    Dim errNum As Long, errSrc As String, errDesc As String

    prevScreen = Application.ScreenUpdating
    prevCalc = Application.Calculation
    prevEvents = Application.EnableEvents

    On Error GoTo Cleanup
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False

    ' ここに本処理

Cleanup:
    errNum = Err.Number
    errSrc = Err.Source
    errDesc = Err.Description
    On Error Resume Next
    Application.ScreenUpdating = prevScreen
    Application.Calculation = prevCalc
    Application.EnableEvents = prevEvents
    On Error GoTo 0
    If errNum <> 0 Then Err.Raise errNum, errSrc, errDesc
End Sub

ポイントは3つです。

  1. 変更前の値を変数に保存し、その値で戻す
  2. 正常終了もエラーも、同じCleanupに流す。Cleanupの前にExit Subを置かない
  3. Cleanupでは、先にエラー情報を退避してから復元し、最後にエラーを再送出する

再送出するのは、呼び出し元にエラーを伝えるためです。設定だけ戻して、エラーの扱いは呼び出し元に任せます。

実務のマクロでも、エラー時の集約先で設定を戻す形にしています。

実機で確認した結果

わざとエラー9を起こし、形ごとに終了後の設定を比べました。

形結果
素朴形・正常終了3つとも戻る
素朴形・エラー画面=False／計算=手動／イベント=Falseのまま
Cleanup形・正常終了3つとも戻る
Cleanup形・エラー3つとも戻り、エラー9が呼び出し元に伝わる
固定値で戻す形元が手動計算でも、自動に変わる

同じ条件を、再現用の最小コードで実行した結果です。

VBAのイミディエイトウィンドウ。素朴形はエラー後に画面=False・計算=手動・イベント=False、Cleanup形は3つとも元の設定に戻った結果
再現用の最小コードの実行結果

Cleanup形でエラー9を起こし、エラーダイアログで「デバッグ」を押したあと、停止して確認しました。3つとも戻ったままでした。

ただし、黄色く表示される行は、元のエラー行ではなく、再送出のErr.Raiseの行です。元のエラー行を探すときは、エラー番号と説明文を手がかりにします。

よくある失敗

1. 戻す行を末尾にだけ書く

エラーが起きると、その行を通りません。素朴な形では、3つとも変更されたまま残りました。

2. 固定値で戻す

TrueやxlCalculationAutomaticで戻すと、もともと手動計算だった場合に自動へ変わります。変更前の値を保存して戻せば、元の状態に戻ります。

3. Cleanupの先頭でOn Error Resume Nextを書く

On Error Resume Nextを実行した後は、Err.Numberが0になりました。退避する前に書くと、エラー番号が0になり、呼び出し元にエラーが伝わりません。

On Error Resume Nextでエラーを握りつぶすと、通信の失敗がStatus 0としか見えなくなることもあります。XMLHTTPでSendが失敗した例は、【VBA】GAS宛POSTのStatus 0をサイズ別に実測で扱っています。

4. 共通のSubを呼んだあとでErrを読む

後片付けを別のSubに切り出した場合、そのSubを呼んだ後でErr.Numberが0になることがあります。エラー9が起きた直後に、中身の違う補助Subを呼び、呼んだ後のErr.Numberを比べました。

補助Subの中身呼んだ後のErr.Number
On Error文を含まない（代入だけ）9のまま
On Error Resume Next0
On Error GoTo 00
On Error GoTo ラベル（ラベルには進まない）0
Err.Clear0
On Error Resume Nextのあとで0除算（エラー11）が起きる11
応用例のLeaveFastMode（On Error Resume Nextまで進む経路）0
応用例のLeaveFastMode（On Error Resume Nextの前でExit Subする経路）9のまま（Descriptionも残る）

呼び出し元がエラーハンドラの中にいる場合と、On Error Resume Nextでエラーを受けた直後の場合で、結果は同じでした。2回続けて実行して、結果は一致しました。今回の環境（Excel 16.0、Windows 64bit）での結果で、他のバージョンは未検証です。

補助Subの中でOn Error文やErr.Clearが実行されると、呼び出し元のErrも書き換わります。応用例のLeaveFastModeはOn Error Resume Nextを含むため、HelperMacroは呼ぶ前にエラー情報を退避しています。Errの退避は、Subを呼ぶ前に済ませます。

5. Escキーで中断すると、後片付けは動かない

実行中にEscキーで止めて「終了」を押すと、Cleanupは実行されませんでした。画面=True、計算=手動、イベント=Falseのままです。

応用例

共通のSubにまとめる

複数のマクロで同じ後片付けを書くなら、変更前の値を持つ型を用意し、入るときと出るときの2つのSubに分けると短く書けます。型の宣言は、モジュールの先頭に置きます。プロシージャの途中に書くと、コンパイルエラーになりました。

Option Explicit

Private Type AppState
    Saved As Boolean
    ScreenUpdating As Boolean
    Calculation As XlCalculation
    EnableEvents As Boolean
End Type

Private Sub EnterFastMode(ByRef st As AppState)
    st.ScreenUpdating = Application.ScreenUpdating
    st.Calculation = Application.Calculation
    st.EnableEvents = Application.EnableEvents
    st.Saved = True
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
End Sub

Private Sub LeaveFastMode(ByRef st As AppState)
    If Not st.Saved Then Exit Sub
    On Error Resume Next
    Application.ScreenUpdating = st.ScreenUpdating
    Application.Calculation = st.Calculation
    Application.EnableEvents = st.EnableEvents
End Sub

Sub HelperMacro()
    Dim st As AppState
    Dim errNum As Long, errSrc As String, errDesc As String

    On Error GoTo Cleanup
    EnterFastMode st

    ' ここに本処理

Cleanup:
    errNum = Err.Number
    errSrc = Err.Source
    errDesc = Err.Description
    LeaveFastMode st
    If errNum <> 0 Then Err.Raise errNum, errSrc, errDesc
End Sub

別のマクロから呼ぶ場合

変更前の値で戻す形なら、呼ばれた側がエラーで止まっても、呼び出し元の高速化は崩れません。固定値で戻す形では、呼び出し元の設定まで戻してしまいました。

戻らなくなったときの保険

Escキーでの中断などに備えて、設定を既定に戻すだけのマクロを1つ用意しておくと安心です。

Sub ResetState()
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
End Sub

エラー時の後片付けを、シートを別ブックにコピーして配布用ブックを作るマクロに入れた例は、【VBA】シートを別ブックにコピーして配布用ブックを作る にまとめています。一時シートの削除と DisplayAlerts の復元を、Cleanup で行う形です。

後片付けが必要なものは、設定だけではありません。進捗表示のUserFormを開いたまま終わると、フォームが画面に残ります。その場合の後片付けは、【VBA】非モーダルUserFormで進捗表示を作る方法 で測っています。

まとめ

エラーで止まったときも設定を戻すには、次の3点です。

  • 変更前の値を保存して、その値で戻す
  • 正常終了もエラーも、同じCleanupに流す
  • エラー情報は、On Error Resume Nextより先に退避する

Escキーでの中断は防げないので、リセット用のマクロも持っておくと安心です。

速くするための設定ほど、戻す側の設計が大切です。

コメント

タイトルとURLをコピーしました