Choose your language

VBA Error Handling


When running a VBA program, things not always go as expected. Sometimes errors will occur, and it is necessary to know how to deal with them.


Types of errors

In VBA 3 types of errors can be found:


Syntax Error

These are errors that occur when the syntax of any line is incorrect.

E.g.: If statement without Then; or typos, such as Thn instead of Then.

No execution is required to identify this type of error. Normally the VBE itself sends out a warning as soon as the line is changed (with Enter or the mouse).

To correct this error, simply adjust the syntax, noting if everything was spelled correctly in the expected order.


Compile Error

These are errors that occur when the syntax of the lines is correct, but the logical set of the code is faulty.

Examples:

  • An If statement within a loop with the End If statement outside the loop

        For i = 1 To 10
        'Tasks...
        If i = 9 Then
        Next i
        End If
    

  • An End If statement after the End Sub

    Sub example()
        If i = 10 Then
    End Sub
        End If
    

A warning is displayed when a code with this type of error is executed.

To deal with this type of error it is enough to correct the order of the lines. The correct order will guarantee that any statement that needs a counterpart (Ex: For, If, Sub) has its closure before another statement that also needs a counterpart.


Runtime Error

These are errors that occur when the program, during execution, can not run a line of code because of improper associations or incompatibilities.

E.g.: 1/0 will result in a 'Division by zero' error.

There are two main approaches (which may be complementary) to handling with this type of errors:

Next, we will focus on the handling of Runtime Errors with the statement On Error.


On Error Statement

On Error is the main form of handling a VBA "Runtime Error". This informs VBA what to do when such an error occurs.

On Error can be used in three ways:

When using an On Error statement, VBA will set a new default action to deal with errors for the next lines of code that will be executed.

To reset to the Excel standard use On Error GoTo 0.


On Error GoTo <Label>

When an error occurs, the execution will jump to a defined line, usually at the end of the code:

Sub ErroGoTo()

    On Error GoTo EH

    X = 1 / 0 'Causes an error

    '
    ' Sub Code
    '

Exit Sub
EH: 'Error Handling
    MsgBox "The following error occurred: " & Err.Description
End Sub

In this example, when the error is detected, VBA shifted its execution to the line with the label (EH).

Notice that in order to not execute the error handling without any error occurred, the instruction Exit Sub was added before EH:

The variable Err.Description will contain the description of the error, if it occurs. Division by zero, in this example.

Err Description

EH is a label used in this example. You can use any name for this label, as if it were a variable name.


On Error Resume Next

To continue the execution of the code even in case of an error, use the code On Error Resume Next.

    On Error Resume Next

    X = 1 / 0 'Causes an error, but execution will not stop

If X needs to have a value, it can be established as follow.

    On Error Resume Next

    X = 1 / 0 'Causes an error, but execution will not stop

    If Err.Number <> 0 Then 
        X = 1
    End If

Notice that Err.Number will return a number other than 0 if an error occurs.

Whenever you use the On Error Resume Next statement, make sure that the code is predicting the missing variables or lines of code that may cause problems. This is done to not get unpredictable results.


On Error Goto 0

On Error Goto 0 is the default option of VBA. In case of error, the execution will be interrupted and an error message will be displayed:

    X = 1 / 0    'Causes an error

On Error Standard

This statement is generally used when you want to reset to the default form of VBA error handling after a previous specification:

    On Error Resume Next

    X = 1 / 0 'Causes an error, but execution will not stop

    If Err.Number <> 0 Then 
        X = 1
    End If

    'Until now errors have been bypassed

    On Error Goto 0 'From here on errors will be displayed

    Y = 1 / 0

    'It will display the error message for Y


SuperExcelVBA.com is learning website. Examples might be simplified to improve reading and basic understanding. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. All Rights Reserved.

Excel ® is a registered trademark of the Microsoft Corporation.

© 2019 SuperExcelVBA | ABOUT

Protected by Copyscape