Excel VBA Error Handling in Loop (5 Best Practices)

Get FREE Advanced Excel Exercises with Solutions!

VBA (Visual Basic for Applications) is a powerful tool that allows developers to create complex macros and automate various tasks in Microsoft Excel. However, as with any programming language, errors can occur during the execution of VBA code. When working with loops, it is essential to handle errors properly to avoid unintended consequences, such as an infinite loop, data corruption, or undefined mathematical calculations. In this article, we will explore the concept of VBA error handling in the loop and provide examples of how to use various error-handling techniques to ensure that your code runs smoothly and efficiently.

vba error handling in loop


How to Launch VBA Editor in Excel

For inserting any VBA code, open the VBA window first. For that, just click on the Developer tab and select Visual Basic from the appeared options. Also, you can open the VBA window by pressing the keyboard shortcut 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 any code, just press the Run button from the VBA window, as in the screenshot. Also, you can press the keyboard shortcut F5.

Step to run vba code.


What Is Error Handling in 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. These are-

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

Excel VBA Error Handling in the Loop: 5 Best Practices

In this segment, we will introduce different types of errors found in examples of VBA code and show ways to solve them. Obviously, our errors will be related to the different VBA loops, like the For Loop or the Do While Loop. Without further delay, let’s walk through the problems and their respective solutions.


1. Use of On Error GoTo Command to Handle Error in For Loop

In our first example, let’s start with the errors while running a loop. In this example, we introduced an infinite mathematical expression for one iteration of the loop. And we will avoid the error using the On Error GoTo Label statement.

For analysis, 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:

  • Firstly, we introduced a sub-procedure named For_Loop_GoTo_Label.
  • Then, we declared a variable i as an integer.
  • Afterward, we used error handling and specifies that if an error occurs, the macro should jump to the ErrorHandler label.
  • Next, 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.

Now, let’s explore how the error handler works!

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

result of for loop iteration without error

  • Again, 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


2. Use of On Error Resume Next to Avoid Error in For Loop

Now, let’s use another error-handling tool in VBA. The On Error Resume Next command helps to avoid the error in the code and jump to the next instruction. Why not use it in the For loop?

Apply the following code in a new module of the VBA window to understand the use of this trick.

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”


3. Handle Step Size Error in 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 for a better understanding of the method.

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:

  • Here, we used the Resume Next command again.
  • K = 1 / 0, This line 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


4. Apply On Erro GoTo 0 Statement to Handle Error in VBA Do While Loop

Do While loop is also widely used in VBA for iteration. So errors can be induced in this type of loop as well. Let’s solve a problem in Do While loop with GoTo 0 statement. 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 from further proceedings and showing the details of the error.

Error handled by goto 0 statement

Read More: How to Handle Excel VBA On Error GoTo 0 


5. Use On Error GoTo Label Command for Error Handling in Do While Loop

Let’s use the well-known GoTo Label statement in the Do While loop. The procedure is the same as before.

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 codes, here 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

When writing Excel VBA codes, error handling is an essential part of ensuring that the macro runs smoothly and does not cause any unexpected errors. Here are some 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

1. How do you handle specific errors in VBA?

A: 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.

2. How do I create an error handler in VBA?

A: 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.

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

A: 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, cause you won’t be notified about the bug in your code. 

Wrapping up

  • In this article, we discussed errors of various types which are related to the loops of VBA (For loop, Do While loop, etc.).
  • We showed the use of On Error GoTo Label and Resume Next commands to avoid errors in those loops.
  • We covered another code statement On Error GoTo 0 which halts the code after the occurrence of an error.

Download Practice Workbook

You can download the practice workbook from here.


Conclusion

While running any loop like For loop or Do While loop etc. in VBA code, it’s quite common to have errors of various types. Here, we introduced a few errors in the codes and showed some examples of VBA error handling in the loop. Hopefully, it will help you to avoid errors in your code writing, especially when using VBA loops. Please leave comments if you have queries or suggestions.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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