How to Include Error Handling in VBA Code

For Microsoft® Access, Excel and Word, how to include error handling in Visual Basic for Applications (VBA) code to intercept and process errors when they occur.

Last updated on 2022-10-23 by David Wallis.

Preface

A VBA procedure is a coded instruction to an application such as Excel to perform a task.

Errors occur during the execution of a VBA procedure due to a variety of reasons, including the use of incorrect code or the code attempting to run under circumstances for which it was not intended.

Introducing error handling (error trapping) in all your VBA procedures puts you in control of errors when they occur — puts you in the position to do something about them before they cause any harm.

By managing errors you can spare your users from being thrown into a panic at not knowing how to respond to a panic-inducing error messages, like this one;

Failure to include error handling may result in unwelcome and undesirable behaviour of the software. Your users could be faced with corruption of their data.

Types of Procedures

Errors may occur during the execution of the two common VBA procedures, Function Procedures and Sub Procedures.

Function Procedures A Function Procedure will complete a sequence of actions and then return a result to the host applicaton. Think of the SUM function in Excel: it acts to add up the numbers in a block of cells and returns the total to your spreadsheet.

Sub Procedures A Sub Procedure performs a sequence of actions to complete a task, but does not return a result. Say, you write a procedure to format your worksheet. When you run it, the procedure adjusts fonts, colours and so on, but you don’t intend it to return a value, like SUM would.

Simple Sub Procedure Error Handler

There are a number of ways in which you might choose to include code for error handling in a macro. This is the skeleton code for a simple way:

The On Error statement turns on error trapping. Information about any error that occurs subsequent to this statement is stored in a VBA error object named Err.

In the event of an error, the On Error Goto errHandler statement instructs the procedure to stop executing your code at the point at which the error occurs, to skip to the errHandler label and to resume execution there.

MsgBox Err.Number & ": " & Err.Description sends a message box to the screen displaying information about the nature of the error:

• Err.Number is a unique identification number for the error object drawn from VBA’s library of errors
• Err.Description is a description of that error.

Resume procDone instructs the procedure to resume executing at the procDone label.

Refined Error Handling Code

Let’s assume you have wrapped a new procedure in the error-handling code described above. As you test your macro an error results. Therefore you are presented with the message box from which you learn the error number and the nature of the error.

Now you are in a position to revise your error handler to respond to this specific error (in this example the error number 1234):

As you test your macro and uncover other potential errors, you can extend the Select Case statement by including other Cases accordingly. In the following refinement of the macro, the messages to users are made specific:

If when your procedure completes its run you want the user to be given a message about any error, then here is a further refinement of your code:

For users who are adept at computer usage, as developer you will get targeted information about an error if you refine your error handler even further:

The Case Else block responds to any error for which you have not specifically coded. So the bulk of the message it displays is information useful to you, the developer.

NameOfYourProcedure is the name you’ve given to the procedure in which you’ve included this error handler.

Program will now … is your message to the user about what will happen as a result of the error. You’ll adapt this message as you deploy the error handler throughout your code modules.

Take Care—Test to Destruction

Always test your macros fully before putting them into service. Best to test on a back-up copy of your application.

Testing takes time. To the time I estimate for writing the code, I add 30% at least for the testing.

Function Procedure Error Handlers

The error handler for a Function Procedure needs to be tailored to the nature of the result the function is meant to return to the host application.

The function may be designed to return a number, a date or some text, for example. So the result returned by its error handling needs to respond appropriately.

If there is sufficient interest, then I shall publish a piece on this condition.

Your Support for DMW TIPS

Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.

To make a contribution by PayPal in GBP (£ sterling) —

To make a contribution by PayPal in USD ($ US) —

Thanks, in anticipation.