Excel VBA Error Handling in Loop (5 Best Practices)

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

vba error handling in loop


How to Launch the VBA Editor in Excel

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

opening vba window

Note: If your Excel doesn’t have the Developer tab automatically, then you can enable the Developer tab in Excel first.
  • After opening the VBA window, you need to Insert a new Module.

inserting new module

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

Step to run vba code.


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

Code to use GoTo Statement for error handling

Code Breakdown:

  • We introduced a sub-procedure named For_Loop_GoTo_Label.
  • We declared a variable i as an integer.
  • We used error handling and specifies that if an error occurs, the macro should jump to the ErrorHandler label.
  • We run a For Loop for the range of i from -1 to 1.
  • For each iteration of the loop, we calculated x as 10/i which becomes undefined when i = 0.
  • The error is handled by the GoTo Label statement. During an error instance, the code refers to the ErrorHandler block which shows the error description in a MsgBox.
  • After running the code, you’ll see the output of the division is -10.

result of for loop iteration without error

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

result of for loop iteration with error handled with goto statement

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

result of for loop iteration without error


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

Code to use Resume Next for error handling

Code Breakdown:

  • On Error Resume Next, this line enables error handling and specifies that if an error occurs, the macro should continue executing the next line of code without stopping.
  • We took a range of i from 6 to 10 for running the loop 5 times as well as using the value of i.
  • Inside the loop, we assigned a value to the cell located in column 6 of the current row (i) using the Cells property. The value is obtained by looking up the value of the cell located in column 5 of the current row using the VBA VLookup function of the WorksheetFunction object. The Range object “B:C” specifies the lookup table range and the number 2 specifies the column number to return. The last argument 0 specifies an exact match.

Output of VLookup in for loop with error handled

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

Err.Number helps to show the error number

Code Breakdown:

  • We used the Resume Next command again.
  • K = 1 / 0, assigns a value to the variable K, but it also causes a runtime error by attempting to divide 1 by 0, which is not possible.
  • The If Then block of code checks if there was any error raised in the previous line. If there was an error, it displays a message box indicating the error number and sets the value of K to 2, which is some minimum value of K to be used if there is an exception in the code.
  • The last block of code starts a loop that iterates from 1 to K. During each iteration, it multiplies the current value of y by the current value of j and then assigns the result back to y. It also displays a message box indicating the result of the multiplication operation.

step size of for loop is redefined after error detection


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

iteration of while loop without error

In the code, the Do While loop works just fine except for i = 5, and the GoTo 0 statement resolves the issue by halting the code and showing the details of the error.

Error handled by goto 0 statement


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

while loop iteration without error

The Code is similar to the previous ones. We introduced an error in the Do While loop with iteration for i = 5, and the error is handled by the GoTo Label Statement. Also, the ErrorHandler block shows the error number and details of the error with the help of Err.Number and Err.Description command.

error in while loop handled with goto label

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

Get FREE Advanced Excel Exercises with Solutions!
Mehedi Hasan Shimul
Mehedi Hasan Shimul

Md. Mehedi Hasan, with a BSc in Electrical & Electronic Engineering from Bangladesh University of Engineering and Technology, holds a crucial position as an Excel & VBA Content Developer at ExcelDemy. Driven by a deep passion for research and innovation, he actively immerses himself in Excel. In his role, Mehedi not only skillfully addresses complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his steadfast commitment to consistently deliver exceptional and quality content.... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo