How to Use Try-Catch in VBA

1. Use On Error Resume Next to Handle Errors in VBA
2. Use On Error GoTo 0 to Handle Errors in VBA
3. Use On Error GoTo [Label] to Handle Errors in VBA

The Try-Catch method prevents program crashes when an internal error occurs in computer programming. It is useful to prevent system errors and let the smooth flow of code execution occur.

However, unlike other programming languages, VBA does not have the Try-Catch block. On the good side, it has workaround methods designed explicitly for error handling procedures.

In this article, error handling procedures in VBA will be discussed and demonstrated together with several samples that would help you implement error handling schemes in your VBA project.

Error handlers are an important part of your code. They are our go-to guy when the VBA compiler cannot handle things.

Listed below are the VBA error handling methods:

1. On Error Resume Next - will ignore any encountered error, and the code will continue to run.
2. On Error GoTo 0 - will stop the code on the line that causes the error and show a message box describing the error.
3. On Error GoTo [Label] - you can specify what you want to do if your code has an error.

We will discuss all of these error-handling routines in detail below.

Use On Error Resume Next to Handle Errors in VBA

This error handler allows the compiler to ignore the error and execute the next code line. It is the frequently used error handler in VBA as it does not need any complicated code to implement.

However, when using this error handler, proceed with precaution since it ignores errors. The code execution might not execute as planned with these undetected errors.

On Error Resume Next is best to know what kind of errors you are likely to encounter. And then, if you think it is safe to ignore these errors, you can use this.

The code block below will output an error without error-handling routines because division by zero is not allowed mathematically.

Sub DivideNumbers()

Dim x,y,z as integer

x = 10/4
y = 15/0
z= 2/5

Debug.Print x & vbNewLine & y & vbNewLine & z

End Sub

DivideNumbers Output:

Error: ! Runtime Error '11':
         Division by Zero

Using the On Error Resume Next error handler:

Sub DivideNumbers()
    On Error Resume Next
 
Dim x, y, z As Integer

x = 10 / 4
y = 15 / 0
z = 2 / 5

Debug.Print x & vbNewLine & y & vbNewLine & z

End Sub

DivideNumbers Output:

2.5

0

The DivideNumbers output shows that the second line was skipped since a Division by Zero error occurred on that line.

Use On Error GoTo 0 to Handle Errors in VBA

The On Error GoTo 0 error handler resets the default error behavior of the compiler.

So why still use it? On Error GoTo 0 error handler addresses the risk of non-detection of errors induced by On Error Resume Next error handler. Thus, On Error GoTo 0 is typically used in conjunction with On Error Resume Next to enable error detection on the code section where no error should occur.

To have a better explanation, see the code block below:

Sub ErrorSub()
    On Error Resume Next

'An error could happen in this area but will not be detected.

    On Error Goto 0

'Once an Error occurs, an error message will appear.

End Sub

For a better example, see below code block.

Sub DivideNumbers()
    On Error Resume Next

Dim x, y, z, a, b, c

x = 3 / 2
y = 0 / 0
z = 2 / 6

Debug.Print x & vbNewLine & y & vbNewLine & z
    On Error GoTo 0

a = 8 / 7
b = 2 / 0
c = 2 / 7

Debug.Print a & vbNewLine & b & vbNewLine & c
End Sub

DivideNumbers Output:

1.5
0.333333333333333

Then an error occurred since the On Error Resume Next was negated by the On Error GoTo 0 on the second part of the code.

Error: ! Runtime Error '11':
         Division by Zero

Use On Error GoTo [Label] to Handle Errors in VBA

The two error handlers above don’t handle errors; either they skipped or neglected the error. The third error handler, On Error GoTo [Label], will allow you to deal with the error and let you choose the action to be taken when an error occurs.

There are several best practices in using On Error GoTo [Label], one is to terminate the code execution when an error occurs, and the other is to correct the error, then retry, and many more. The example below will demonstrate the usage of On Error GoTo [Label].

The code block below will automatically terminate the code when an error occurs.

Sub DivideNumbertoNumber()
    On Error GoTo ErrHandler

Dim i As Integer

For i = 5 To -5 Step -1
Debug.Print i / i

Next i

ErrHandler:

End Sub

DivideNumbertoNumber Output:

 1 
 1 
 1 
 1 
 1 

As observed, code execution stopped when i = 0 since i divided by i would mean 0 divided by 0, which would result in an overflow.

In the next example, we will demonstrate how to print in the Immediate Window if an error occurs. We can also use the Err.Description property to print the actual error.

Sub DivideNumbertoNumber()
    On Error GoTo ErrHandler

Dim i As Integer

For i = 5 To -5 Step -1
Debug.Print i / i

Next i

Exit Sub

ErrHandler:
Debug.Print "An Error occured at i is equal to " & i & vbNewLine & "Error Type: " & Err.Description

End Sub

DivideNumbertoNumber Output:

 1 
 1 
 1 
 1 
 1 
An Error occured at i is equal to 0
Error Type: Overflow

Note that Exit Sub was placed before the ErrHandler to exit the subroutine before it reached the ErrHandler for the cases that error did not occur. If the Exit Sub were not inserted, the compiler would execute the ErrHandler even if there is no error.