Introduction
When an error occurs while running a VBA macro, the program usually stops. However, there are times when you might think, “It’s okay if an error happens here, so please ignore it and move on to the next line.”
A typical example is “deleting a previously created report sheet at the beginning of a macro.” When you run the macro for the first time, that sheet does not exist yet, so trying to delete it will cause an error.
The On Error Resume Next statement allows you to intentionally ignore such “acceptable errors.”
This article explains the basic usage of On Error Resume Next and important points to use it safely.
Sample Code Using On Error Resume Next
This macro first deletes the sheet named “Summary” if it exists, and then creates a new “Summary” sheet at the beginning. Even if the “Summary” sheet does not exist initially, the process continues without stopping due to an error.
Complete Code
' 「Summary」シートを初期化（あれば削除し、なければ新規作成）する
Sub InitializeSummarySheet()
'--- 1. シート削除時の確認メッセージを非表示にする ---
Application.DisplayAlerts = False
'--- 2. これ以降のエラーを無視するように設定 ---
On Error Resume Next
'--- 3. エラーが発生する可能性のある処理 ---
' "Summary"シートが存在しない場合、この行でエラーが発生するが、無視される
ThisWorkbook.Worksheets("Summary").Delete
'--- 4. エラーハンドリングを通常の状態に戻す ---
' この行が非常に重要！
On Error GoTo 0
'--- 5. 確認メッセージを再表示する設定に戻す ---
Application.DisplayAlerts = True
'--- 6. 新しいシートを追加して名前を変更 ---
Worksheets.Add(Before:=ThisWorkbook.Worksheets(1)).Name = "Summary"
MsgBox "「Summary」シートを初期化しました。"
End Sub
Code Explanation
Application.DisplayAlerts = False
When deleting a sheet, Excel displays a confirmation dialog asking, “Do you want to delete this?”. Application.DisplayAlerts = False is a command to temporarily hide such confirmation messages from Excel. Be sure to set it back to True after the process is finished.
On Error Resume Next
This single line is the declaration to ignore errors. VBA will not stop the program even if a runtime error occurs after this line. Instead, it proceeds to the line immediately following the one where the error occurred, as if nothing happened.
On Error GoTo 0
This is the most important rule when using On Error Resume Next. The effect of On Error Resume Next continues until it is canceled by On Error GoTo 0 or until the procedure ends. If you forget to cancel it, all subsequent unexpected and serious errors will also be ignored.
This makes debugging extremely difficult. Therefore, it is strongly recommended to write On Error GoTo 0 immediately after the process where you want to ignore errors, returning VBA to its normal error handling state.
Summary
In this article, I explained how to intentionally ignore specific errors using On Error Resume Next.
On Error Resume Next: Continues processing from the next line even if an error occurs.On Error GoTo 0: Cancels the setting to ignore errors and returns to the normal state. These two must always be used as a set.- It is important to keep the range where errors are ignored to the absolute minimum necessary.
On Error Resume Next is a very convenient statement if used correctly, but it can become a source of bugs if used without a plan. Always be conscious of the scope of its effect and use it in a limited manner.
