Handling VBA Errors Using On Error Statements

Buzz

Content

Explore VBA Error Handling with On Error Commands

If you frequently work with VBA, encountering errors is inevitable. Therefore, knowing how to trap errors when coding and handling common issues is crucial. Refer to the content below to learn how to manage VBA errors using On Error statements.

Errors in VBA programming in Excel are unavoidable. The key is to identify the cause of the error and handle it effectively. One useful approach is to use certain On Error statements for VBA error handling.

Programmers can manage VBA errors using the On Error statement

Explore VBA Error Handling with On Error Commands

When your code encounters an error, you have two options:

Option 1: Ignore the error and let the code continue running.

Option 2: Implement on-the-spot error handling code to execute when an error occurs.

Additionally, we can utilize various On Error commands to troubleshoot errors.

1. On Error Resume Next

By using the On Error Resume Next statement in the code, encountered errors will be ignored, allowing the code to continue execution.

While this error-handling method is commonly employed, caution is advised as it overlooks potential errors, making it challenging to identify and rectify specific issues.

For instance, running the code snippet below will result in an error:

Sub AssignValues()
x = 20 / 4
y = 30 / 0
End Sub

The error occurs because dividing a number by 0 is not possible.

However, using the On Error Resume Next statement in the above code will suppress the error, making it challenging to identify which error needs fixing:

Sub AssignValues()
Handle errors with caution
x = 20 / 4
y = 30 / 0
End Sub

Note: Only use the On Error Resume Next statement when we are certain about the type of error that VBA code will ignore and skip.

For example, the following VBA event code adds date and time values to cell A1 of a newly inserted sheet (this code is added to the worksheet, not a module):

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Sh.Range('A1') = Format(Now, 'dd-mmm-yyyy hh:mm:ss')
End Sub

Although the above code functions well in most scenarios, introducing a chart instead of a sheet will result in an error. This is because charts lack cells.

If the On Error Resume Next statement is utilized in the code snippet above, it will execute smoothly without encountering any errors.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
On Error Resume Next
Sh.Range('A1') = Format(Now, 'dd-mmm-yyyy hh:mm:ss')
End Sub

If desired, we can analyze errors (if any) and display relevant error messages.

Utilize the code snippet below to display an error message dialog, notifying the user that a worksheet has not been inserted:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
On Error Resume Next
Sh.Range('A1') = Format(Now, 'dd-mmm-yyyy hh:mm:ss')
If Err.Number <> 0 Then
MsgBox 'You have inserted a chart' & vbCrLf & 'Error - ' & Err.Description
End If
End Sub

Where Err.Number is used to retrieve the error code and Err.Description is used to obtain the error description.

2. On Error GoTo 0 Statement

The On Error GoTo 0 statement will halt the code at the line causing the error and display a dialog with the error description.

In simple terms, this statement allows checking error behavior and displaying the default error message.

Typically, the On Error GoTo 0 statement is not necessary, but it can be used in conjunction with On Error Resume Next for better results.

For a clearer understanding, readers can refer to the example below. The code snippet will select all empty cells within the range:

Sub SelectFormulaCells()
Choose cells with formulas
Selection.SpecialCells(xlCellTypeBlanks).Select
End Sub

In case there are no blank cells in the selected range, it will result in an error. To prevent errors, we can use the On Error Resume Next statement:

Sub SelectFormulaCells()
On Error Resume Next
Selection.SpecialCells(xlCellTypeBlanks).Select
End Sub

Issues arise if a portion of the code encounters an error, so we use the On Error Resume Next statement to skip errors and move to the next line.

When using this command, we reset the error settings to the default state, which means errors will be displayed (if any).

For example, the code snippet below will not show an error in case there are no blank cells, but it will display an error message for '10/0'.

Sub SelectFormulaCells()
On Error Resume Next
Selection.SpecialCells(xlCellTypeBlanks).Select
On Error GoTo 0
' .. more code that can contain an error
End Sub

3. On Error Goto Label Command

The essence of the On Error Resume Next and On Error Goto 0 commands is not error fixing but rather allowing the program to bypass errors and continue error checking.

By using the On Error Go [Label] command, you specify what to do in case the code encounters an error.

The code structure employing this command to handle errors looks like the example below:

Sub Test()
On Error GoTo Label:
X = 10 / 0 'this line causes an error
' .... remaining code
Exit Sub
Label:
' code to handle the error
End Sub

Note: Before the Label in the code is Exit Sub. This ensures that if no errors occur, we exit the Sub, and the Label code is not executed. Without using Exit Sub, it would always execute the Label code.

In the example code below, when an error occurs, the code will jump and execute the code in the error handling section:

Error Handling Example:
Sub ErrorHandler() On Error GoTo ErrMsg X = 12 Y = 20 / 0 Z = 30 Exit Sub ErrMsg: MsgBox 'An error occurred' & vbCrLf & Err.Description End Sub

Note: When an error occurs, the code has run and executed the lines before the line causing the error. In the example above, the code sets the value of X to 12, but due to the error in the next line, it does not set values for Y and Z.

When jumping to the error handling code (in the example above, ErrMsg), it will continue to execute all lines within and below the error handling code and exit the Sub.

4. On Error Goto -1 Command

This command is a bit intricate, and in most cases, users rarely utilize it. Here, Mytour will explain how to use the On Error Goto -1 command to handle VBA errors in Excel.

Suppose if your code is encountering an error but has been resolved by using an on-the-spot error handler. However, if another error occurs within that on-the-spot error handler.

In this scenario, you cannot use a second error handler because the first error is unresolved. So, while handling the first error, it still persists in the VBA memory. VBA memory can only hold one error at a time.

The solution in this case is to use the On Error Goto -1 command. This command will clear the error and free up the VBA memory to handle the next error.

For a clearer understanding, refer to some examples below:

The code snippet below will return an error message as it attempts to divide by zero:

Error Handling Example:
Sub ErrorHandler() X = 12 Y = 20 / 0 Z = 30 End Sub

To handle it, I use an error handling code (named ErrMsg) as shown below:

Error Handling Example:
Sub ErrorHandler() On Error GoTo ErrMsg X = 12 Y = 20 / 0 Z = 30 Exit Sub ErrMsg: MsgBox 'An error occurred' & vbCrLf & Err.Description End Sub

Right after the error occurs, the error handler is utilized, and an error message is displayed as shown below:

Next, we expand the code to add code within or after the error handler:

Error Handling Example:
Sub ErrorHandler() On Error GoTo ErrMsg X = 12 Y = 20 / 0 Z = 30 Exit Sub ErrMsg: MsgBox 'An error occurred' & vbCrLf & Err.Description A = 10 / 2 B = 35 / 0 End Sub

While the first error has been handled, the second error hasn't. The screen continues to display an error message as shown below:

However, the code is still functioning as intended. To handle the second error, we use another error handler (ErrMsg2).

Error Handling Example:
Sub ErrorHandler() On Error GoTo ErrMsg X = 12 Y = 20 / 0 Z = 30 Exit Sub ErrMsg: MsgBox 'An error occurred' & vbCrLf & Err.Description On Error GoTo ErrMsg2 A = 10 / 2 B = 35 / 0 Exit Sub ErrMsg2: MsgBox 'An error occurred' & vbCrLf & Err.Description End Sub

If you run the above code, it will still return a run-time error even though we have a second error handler.

This error occurs when we clear the first error from the VBA memory.

When VBA encounters another error, it still associates it with the first error, so the second error handler is not utilized. The code stops at the line causing the error and displays the error message.

To clear the VBA memory and remove the previous error, we will use the On Error Goto -1 command.

When adding this command to the code below and running it, the code will behave as expected:

Error Handling Example:
Sub ErrorHandler() On Error GoTo ErrMsg X = 12 Y = 20 / 0 Z = 30 Exit Sub ErrMsg: MsgBox 'An error occurred' & vbCrLf & Err.Description On Error GoTo -1 On Error GoTo ErrMsg2 A = 10 / 2 B = 35 / 0 Exit Sub ErrMsg2: MsgBox 'An error occurred' & vbCrLf & Err.Description End Sub

Note: Errors will be automatically cleared when the subroutine ends. So, the On Error Goto -1 command can be useful when encountering one or more errors within the same subroutine.

Through this article, Mytour hopes you grasp and know how to handle VBA errors using On Error statements. Additionally, there are many other VBA errors that we've covered in the article Comprehensive Guide to Fixing VBA Errors in Excel, which you should explore and not overlook.

Frequently Asked Questions

What are the key methods for handling VBA errors effectively?

The key methods for handling VBA errors effectively include using On Error Resume Next to ignore errors, On Error GoTo 0 to halt code execution at the error line, and On Error GoTo Label to specify custom error handling procedures. Each method serves a unique purpose in managing runtime errors and ensuring smoother code execution.

How does the On Error Resume Next statement function in VBA?

The On Error Resume Next statement allows your code to continue executing even after encountering an error. While this method can help bypass errors, it requires caution as it may lead to overlooking issues that need fixing. It's essential to ensure that the type of error is known and can be safely ignored.

Can you explain how to implement On Error GoTo 0 in your VBA code?

Yes, implementing On Error GoTo 0 in your VBA code resets the error handling mechanism. This statement halts execution at the line causing the error and displays the default error message. It's particularly useful when you want to revert to standard error handling after previously using On Error Resume Next.

What does the On Error GoTo Label command accomplish in VBA error handling?

The On Error GoTo Label command allows you to direct the code execution to a specific error handling section when an error occurs. This command provides a structured approach to manage errors by defining how the program should respond in case of an issue, improving overall code reliability.

How can I clear errors in VBA using the On Error GoTo -1 command?

The On Error GoTo -1 command clears the most recent error in VBA, allowing for subsequent error handling without the interference of previous errors. This command is particularly useful when multiple errors might occur in a subroutine, ensuring the error memory is free for new issues that may arise.

Is it possible to handle multiple errors in a single VBA subroutine effectively?

Yes, handling multiple errors in a single VBA subroutine can be achieved using On Error GoTo for specific error handling labels. By implementing error handlers sequentially, such as On Error GoTo -1 to clear previous errors, you can ensure that each error is appropriately addressed without confusion.

Mytour's content is for customer care and travel encouragement only, and we are not responsible.

For errors or inappropriate content, please contact us at: [email protected]