[VBA] Basic Error Handling: How to Use On Error GoTo

When running a VBA macro, if you try to select a sheet that doesn’t exist or divide a number by zero, an error occurs, and the program stops forcibly. This is not a good experience for the user.

By using the On Error GoTo statement in VBA, you can prevent the program from stopping and instead jump to a specific error-handling code block when a “runtime error” occurs. This allows you to politely inform the user about the error and end the macro safely.

This article explains the most basic method of error handling in VBA using On Error GoTo.

Sample Code Using On Error GoTo

This macro attempts to open a worksheet named “Data”. If the sheet exists, it displays a success message. If it does not exist, the code jumps to the error handling block and displays an error message.

' Basic error handling using On Error GoTo
Sub BasicErrorHandlingExample()

    '--- Set up Error Handler ---
    ' If an error occurs after this line, jump to "ErrorHandler:"
    On Error GoTo ErrorHandler
    
    '--- Normal Processing ---
    ' Execute a process that might fail (e.g., activating a sheet)
    Worksheets("Data").Activate
    
    ' This line runs only if no error occurred
    MsgBox "Process completed successfully.", vbInformation
    
    ' On success, skip the error block and exit the Sub
    Exit Sub

'--- Error Handling Block ---
' Label name is arbitrary. Must end with a colon (:)
ErrorHandler:
    ' This block runs only if an error occurred
    MsgBox "An error occurred." & vbCrLf & "The sheet named 'Data' was not found.", vbCritical, "Error"
    ' The Sub ends automatically after this

End Sub

Explanation of the Code

On Error GoTo ErrorHandler

This line declares the start of error handling. If a runtime error occurs anywhere after this line, VBA immediately stops the current line and jumps to the location labeled ErrorHandler:.

Exit Sub

This is a very important line. If you do not include Exit Sub, the code flow will continue down into the ErrorHandler: block even if the process finished successfully. Exit Sub forces the procedure to end after normal completion, ensuring that the error handling code is skipped when there are no errors.

ErrorHandler:

This is the label that marks where the code should jump when an error occurs. You can give it any name you like, but it must always end with a colon (:). Between this label and End Sub, you write the instructions you want to execute when an error happens, such as displaying a message box or resetting variables.

Summary

In this article, we explained the basic structure of error handling using On Error GoTo in VBA.

1. Declare On Error GoTo LabelName at the beginning of your procedure.
2. Write Exit Sub at the end of your normal code to skip the error block.
3. Write LabelName: at the end of the procedure to define the error handling actions.

Mastering this three-part structure will significantly improve the stability of your macros and help you create tools that are much friendlier for users.