Office tutorials

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 efficiently. In VBA, as in any other program writing errors appear. They may result, among others from the incorrect syntax or logic of the program. Errors that may occur in VBA can be divided into:

  • Syntax errors
  • Logic errors
  • Errors in the execution time of the so-called Runtime Errors
  • Object errors

Each of the above errors can be properly diagnosed and handled in the VBA code. We can find the error number and its description.

VBA error handling – Error Handling

Error handling in VBA consists mainly in writing the so-called Handler , which is the code that triggers a specific action after the error occurred. How to write such a program? The syntax of the error handling code is described below:

2.1. Resume Next – By entering the On Error Resume Next code at the beginning of the program , we instruct VB to ignore all errors. So, when the error occurs, the program will skip it and go to the next line of code. Contrary to appearances, this is a very useful instruction, for example when creating automatic reports.

2.2 GoTo Line – This is the command to refer to the so-called Handler . At the time of the error, the program using the VBA GoToinstruction jumps to the code block in which the details of the error are described. We are here using the function Err determine the error number , a description of the error , a reference to the relevant section in the help file. After handling the error, we can clear it using the Err.Clear command . A specific example of error handling with the help of the dealer is given below:

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

The article is part of the Excel vba course. The whole course can be found at this link: VBA course . Below is the file with the program code:

Leave a Reply

Your email address will not be published.