Excel VBA Error Handling in Loop (5 Best Practices)

Here’s an overview of how VBA handles errors while performing loops.

How to Launch the VBA Editor in Excel

• Click on the Developer tab and select Visual Basic. Alternatively, press Alt + F11.

• After opening the VBA window, you need to Insert a new Module.

• For running the code, press the Run button from the VBA window or press the keyboard shortcut F5.

What Is Error Handling in a VBA Loop?

VBA error handling in the loop is a technique used in programming to catch and handle errors that may occur during the execution of a loop. Error handling is important in the loop because if an error occurs during the execution of a loop, it can cause the program to crash or produce unexpected results. By applying error handling, the program can gracefully handle errors and continue to run without crashing.

You can implement error handling in the loop in VBA by using the On Error statement. The On Error statement is used to enable error handling in VBA and to specify the type of error handling that should be used. The On Error statement has three common statements:

• On Error GoTo line
• On Error Resume Next
• On Error GoTo 0

Excel VBA Error Handling in a Loop: 5 Best Practices

Example 1 – Use of the On Error GoTo Command to Handle Errors in a For Loop

In this example, we introduced an infinite mathematical expression for one iteration of the loop. We will avoid the error using the On Error GoTo Label statement.

• Apply the following attached code to a new VBA module and run it.

Code:

Sub For_Loop_GoTo_Label()
    Dim i As Integer
    On Error GoTo ErrorHandler
    For i = -1 To 1
        x = 10 / i
        MsgBox "The Division Result is: " & x
    Next i
    Exit Sub
ErrorHandler:
    MsgBox "Error encountered: " & Err.Description
    Resume Next
End Sub

• After running the code, you’ll see the output of the division is -10.

• If we press the OK button of the MsgBox, we’ll find that the loop encounters an error.

• As the error handler ignores the error, we’ll get the output of i = 1 after pressing the OK button.

Example 2 – Using On Error Resume Next to Avoid an Error in a For Loop

The On Error Resume Next command helps avoid the error in the code and jump to the next instruction.

• Apply the following code in a new module of the VBA window.

Code:

Sub For_Loop_Resume_Next()
Dim i As Long
On Error Resume Next
For i = 6 To 10
Cells(i, 6).Value = WorksheetFunction.VLookup(Cells(i, 5), _
Range("B:C"), 2, 0)
Next i
End Sub

Read More: Excel VBA: Turn Off the “On Error Resume Next”

Example 3 – Handle Step Size Error in a For Loop

Let’s see a problem where the step size for the loop becomes undefined. Why not detect the erroneous step size before using it for the loop? We can check it with Err.Number in association with the If Then statement.

• Insert the following attached code in a new module and run it.

Code:

Sub For_Loop_Step_Size_Error()
On Error Resume Next
K = 1 / 0 ' Line causing error due to a number being divided by zero
If Err.Number <> 0 Then
  MsgBox "The error number is: " & Err.Number
  K = 2 ' Some arbitrary value of K for any exception in the code.
End If
y = 1
For j = 1 To K
 y = y * j
 MsgBox "The result is: " & y
Next j
End Sub

Example 4 – Apply the On Erro GoTo 0 Statement to Handle Errors in a VBA Do While Loop

This statement simply halts the code after facing an error.

• Insert the following code in a new module and run it.

Code:

Sub DoWhileLoop_GoTo_0()
    On Error GoTo 0
    Dim i As Integer
    i = 1
    Do While i <= 10
        x = 100 / (i - 5)
        If i = 5 Then
            Err.Raise 6, Description:="An error occurred on iteration 5"
        End If
        MsgBox "The result of the math formula is: " & x
        i = i + 1
    Loop
End Sub

Example 5 – Use the On Error GoTo Label Command for Error Handling in a Do While Loop

• Apply the following code in a new module of the VBA window and run it.

Code:

Sub DoWhileLoop_GoTo_Label()
    On Error GoTo ErrorHandler
    Dim i As Integer
    i = 1
    Do While i <= 10
        x = 100 / (i - 5)
        If i = 5 Then
            Err.Raise 6, Description:="An error occurred on iteration 5"
        End If
        MsgBox "The result of the math formula is: " & x
        i = i + 1
    Loop
    Exit Sub  
ErrorHandler:
    MsgBox "Error #" & Err.Number & ": " & Err.Description
    Resume Next
End Sub

Read More: How to Solve Overflow Error in VBA

Best Practices for Error Handling in Excel VBA

• Always Enable Error Handling: Use the On Error statement to enable error handling in your code. This will allow your code to gracefully handle any errors that occur and continue executing the rest of the code.
• Use Descriptive Error Messages: When an error occurs, use descriptive error messages that clearly explain what went wrong and how to fix it. This will help users understand what happened and take appropriate action.
• Use Specific Error Handling Techniques: There are several error handling techniques that can be used in Excel VBA, such as On Error Resume Next, On Error GoTo, and On Error GoTo 0. Use the appropriate technique based on the situation to ensure that the error is handled properly.
• Test Your Error Handling: Before releasing your macro, make sure to test your error handling by intentionally causing errors and verifying that your macro handles them properly. This will help you identify any potential issues and ensure that your macro is robust.
• Use Modular Programming: Break down your macro into smaller, modular functions or subroutines. This will make it easier to debug errors and ensure that error handling is applied consistently throughout the macro.
• Keep Error Handling Code Separate: Keep your error handling code separate from the main code. This will make it easier to read and maintain your code and ensure that error handling does not interfere with the main logic of your macro.
• Document Your Error Handling: Document your error handling code so that future users and developers can understand how errors are handled in your macro. This will make it easier to modify and maintain your code over time.

Frequently Asked Questions

How do you handle specific errors in VBA?

You can handle specific errors in VBA by using the Err object. The Err object contains information about the last error that occurred, including its number and description. You can use the Err object to check for specific error numbers and handle them accordingly.

How do I create an error handler in VBA?

To create an error handler in VBA, you can use the “On Error” statement to define the error handling routine. Within the error handling routine, you can use techniques such as error logging, user notifications, and debugging tools to handle the error and prevent the code from crashing.

Can I use “On Error” statements in nested loops in VBA?

Yes, “On Error” statements can be used in nested loops in VBA. However, it is important to keep in mind that errors that occur within nested loops can be more difficult to handle, as they may require additional error checking and handling to prevent the code from crashing.

Things to Remember

• Save your file as .xlsm before running any code.
• You can make a few statements of the code into comments for understanding the type of errors induced.
• It’s not useful to use the On Error Resume Next statement always, because you won’t be notified about the bug in your code. 

Download the Practice Workbook