Error handling in Excel VBA

VBA Error handling – How to use it?

In this article you will learn what VBA error handling is and how to use it correctly. In VBA, as in any other programming languages errors appear. They may be caused by incorrect syntax or logic of the program. Errors that may occur in VBA can be group into:

  • Syntax errors
  • Logic errors
  • Runtime Errors
  • Object errors

Each of the above errors can be diagnosed and handled in the VBA code. The most important things is error number and its description.

VBA error handling – Error Handling

When you want to handle error in Excel VBA, you need to wrote short application called Handler, which is the code that triggers a specific action after the error occurred. How to write error handler? The short syntax of the error handling code is described below:

On Error { GoTo [ line | 0 | -1 ] | Resume Next }

Resume Next

By entering the On Error Resume Next code at the beginning of the program, you inform VB to ignore all errors. So, when the error occurs, the program will skip it and go to the next line of code. This is very useful instruction, for example when you are creating automatic reports based on Excel.

'OfficeInside.Org
Sub ErrorHandling()

   On Error Resume Next

   'typical error - division by 0
   MsgBox 1 / 0
   MsgBox "Example message"

End Sub
VBA error handling – Error Handling

GoTo Line

This is the command to refer to the called Handler. At the time of the error, the program using the VBA GoTo instruction jumps to the code block in which the details of the error are described. You are using here the function Err determine the error number , a description of the error, a reference to the relevant section in the help file. After handling an error, you can clear it using the Err.Clear command. A specific example of error handling with the help is written here:

'OfficeInside.Org
Sub ErrorHandling2()

   On Error GoTo Error_handler

   'typical error - division by 0
   MsgBox 1 / 0

   MsgBox "Example message"

   Error_handler:
      MsgBox "Error occured" & vbCrLf _
      & "error number: " & Err.Number & vbCrLf _
      & "error description: " & Err.Description & vbCrLf _
      & "help file: " & Err.HelpFile & vbCrLf _
      & "error number in help file: " & Err.HelpContext & vbCrLf _
      & "source: " & Err.Source
      Err.Clear

End Sub
VBA error handling – Error Handling. Error number, error description

GoTo 0

Disables handling of errors by Handler. For example, part of your code should be handled by a handler, and the other part should not. Using the On Error GoTo 0 instruction, you disable the previously declared error handling. Below is an example. In the first case, the divide by zero error was handled, in the second it was not.

'OfficeInside.Org
Sub ErrorHandling3()

   On Error GoTo Error_handler

   'typical error - division by 0
   MsgBox 1 / 0

   MsgBox "Example message"

   Error_handler:
      MsgBox "An error occured"
      Err.Clear

   On Error GoTo 0
   Debug.Print 1 / 0

End Sub

Below is the file with the program codes:

The article is part of the Excel vba course. The whole course can be found at this link: VBA course

Leave a comment

Your email address will not be published. Required fields are marked *

%d bloggers like this: