How to handle errors with a generic error-handling routine in Microsoft VBA procedures

Use this VBA error-handling routine to make sure no errors disrupt the code.

Microsoft Visual Basic for Applications is the underlying development language for Microsoft Office apps. Each of these apps has a unique object model but they share some logic. For instance, all the models offer loops, ifs, error-handling and more.

When using VBA, regardless of the app, consider adding a generic error-handling routine to every procedure when that procedure doesn’t require more specific error handling. If an unexpected error shows up, it won’t break your procedure. Instead, it will display an informative message that you can then use to troubleshoot and handle the error more appropriately.

SEE: Hiring kit: Python developer (TechRepublic Premium)

In this tutorial, I’ll show you a block of VBA code that you can insert into VBA procedures to catch unexpected errors. I’m using Microsoft 365 on a Windows 10 64-bit system, but you can use older versions of Microsoft Office.

What is the VBA On Error statement in Office?

VBA has many debugging tools to help you find errors when troubleshooting. The On Error statement does two things: It responds to an error and then specifies where the flow goes to correct that error. A runtime error occurs when there’s an error in the code. This happens for several reasons, but it mostly occurs due to syntax errors and code requests that VBA can’t fulfill.

The On Error statement uses the following syntax:

• On Error GoTo line
• On Error Resume Next
• On Error GoTo 0

We’ll work with the first syntax, which directs the flow to a special error-handling routine, where line identifies that routine, within the procedure. A generic line follows:

On Error GoTo errHandler

You might see variations of errHandler, but you should readily recognize line when reviewing code. In this case, flow moves to errHandler when an error occurs. If you don’t use an On Error statement, any run-time error that occurs is fatal because execution stops.

Now that you know about the On Error statement, it’s time to use it.

How to use an error-handling routine in Office apps

A generic error-handling routine does nothing to prevent an error. Its job is to expose the error so you can fix the code that generated it. Instead of the error halting the code, which will provide little information on why the error occurred, a generic error-handling routine will display the information you need to fix the error. Even if you believe your code to be error-free, I recommend that you add a generic error-handling routine, such as the one in Listing A.

Listing A

Private | Public Function | Sub | procedurename()

On Error GoTo errHandler

….procedure code…

'object = Nothing

Exit | Function | Sub

errHandler: 'The colon character is required.

MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _

VBE.ActiveCodePane.CodeModule, vbOKOnly, "Error"

'object = Nothing

End Sub

Insert the On Error GoTo line statement before the procedure’s code. After the procedure code, include an Exit statement. Otherwise, VBA will execute the errHandler: routine, which you won’t want.

If the code creates objects, be sure to destroy them, adding the necessary object = Nothing statements before the Exit and End statements. Listing A includes one in each spot, commented out as a reminder, but you might need several.

SEE: Hiring kit: Back-end Developer (TechRepublic Premium)

The errHandler code displays a message that contains the error number, a description and the name of the module. I add it to every VBA procedure and comment out the routine if it becomes a nuisance during development.

Your code will dictate specific handling needs, which you will write into your code. The more you test the code, the more specific handling you will add, usually. But there’s always that chance that an unexpected error will halt the code. For this reason, every procedure should have a generic error-handling routine. This routine won’t be able to correct the error and continue, but it will display helpful information so that you can fix the problem at the source.