Five tips for handling errors in VBA

Effective error handling can mean the difference between a seamless, user-friendly experience and a problem-plagued application. These best practices will help ensure your apps run as intended, without a hitch.

A professional application always includes adequate error-handling routines to trap unexpected errors. Sometimes, the right handling means the user never knows the error occurred. At the very least, error-handling routines should address the problem, share adequate information on what the user should do next, and exit the program (if absolutely necessary) gracefully. You put a lot of effort into writing the procedures that run your custom applications. Why let a runtime error ruin it all? By employing a few best practices, you can improve error handling.

1: Verify and configure error settings

Before you do anything, check the error-trapping settings. VBA, via the Visual Basic Editor (VBE), is flexible and allows you to determine how it responds to errors. To access these settings (shown in Figure A), in the VBE, choose Options from the Tools menu, and click the General tab:
  • Break On All Errors: Stops on every error, even errors following a Resume Next statement.
  • Break On Unhandled Errors: Stops for unhandled errors, but stops on the line calling the class (in class modules) rather than the line with the error, which can be problematic during debugging.
  • Break In Class Modules: Stops at the actual error (line of code), but doesn't work as expected with Err.Raise, which generates an error of its own.

Figure A

error handling settings

Choose the most appropriate error-handling setting.
Well-informed users can change this setting, so I recommend that you include a procedure, similar to the one in Listing A, to your application's startup routine.

Listing A

Function SetErrorTrappingOption()
  'Set Error Trapping to Break on Unhandled Errors.
  Application.SetOption "Error Trapping", 2
End Function

2: Every procedure needs error handling

Occasionally, you'll write a simple procedure where's there's no potential for error. But most procedures should have an error-handling routine, even if it's as basic as this one:

Private | Public Function | Sub procedurename()

On Error GoTo errHandler


  Exit Function | Sub
  MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
   VBE.ActiveCodePane.CodeModule, vbOKOnly, "Error"
End Sub

This is error handling at its simplest. The above handler displays the error number, a description, and the name of the module. Some developers prefer to control the exit by using Resume to point to an exit procedure, which is helpful when performing specific maintenance or cleanup tasks before exiting (see Tip #5).

During the development stage, this basic handler can be helpful (or not; see Tip #3). Later, during the testing phase, you can enhance the basic routine to handle the error or to tell the user what to do next.

3: Control error trapping during development

I just told you to add a generic error-handling routine to every procedure, which can be helpful during development. However, some developers find these generic routines annoying. Here's why. When the debugger encounters an error, one of two things happens:

  • If there's no error-handling routine, the debugger stops at the offending line of code, which can be convenient.
  • When there is an error-handling routine, the debugger executes it, which can make debugging more difficult.

If you're in the camp that finds error handling during the development phase too invasive, you can add a generic handler and comment it out until you're ready for it. That can be a bit of a pain, though. Alternatively, forget the commenting and rely on a constant instead. Specifically, set a global Boolean constant, as follows:

Public Const gEnableErrorHandling As Boolean = False

Then, run each call to the error-handling routine by the constant, like this:

If gEnableErrorHandling Then On Error GoTo errHandler

As long as the constant is False, the code skips the error-handling routine. When you're ready to enable error handling, simply reset the constant to True. The constant method might wear on you too because you have to run every error-handling call by it. In the end, the route you take isn't as important as knowing the alternatives and how to properly implement them.

4: Inhibiting errors

Sometimes, the best way to handle an error is to ignore it. This situation arises when you want to execute a task knowing that it might generate an error, and often, the error is what you're after! For instance, if a subsequent task relies on a specific file, you should test for the file's existence before executing that task. If the statement errors, you know the file isn't available and you can include code that takes appropriate action.

Admittedly, this setup makes some developers cringe -- you are purposely introducing an error into your code. On the other hand, properly handled, it can be a much more efficient route than alternative solutions.

To ignore an error, precede the statement with the Resume Next statement, as follows:

On Error Resume Next

This statement allows the program to continue to the next line of code, totally ignoring the error. In short, Resume Next disables error handling from that line forward (within the procedure). That's the easy part, but you're not done. Anytime you use Resume Next, you need to reset error handling by using the following statement:

On Error GoTo 0

GoTo 0 disables enabled error handling in the current procedure and resets it to Nothing -- that's the technical explanation. In a nutshell, Resume Next skips an error and GoTo 0 tells the debugger to stop skipping errors.

Be sure to insert the GoTo 0 statement as early as possible. You don't want to mask other errors.

5: Handle the exit

Once the error-handling routine completes its task, be sure to route control appropriately:

  • By exiting the procedure
  • By returning control to the procedure

Both strategies are appropriate; the code's purpose will dictate your choice.

Tip #2 contains the simplest error-handling routine. It displays information about the error and exits the procedure. You can control that exit by including an exit routine like this:

Private | Public Function | Sub procedurename()
  On Error GoTo errHandler
  MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
   VBE.ActiveCodePane.CodeModule, vbOKOnly, "Error"
  Resume exitHere
End Sub

Once the error-handling routine is complete, the Resume exitHere statement routes the flow to exitHere. You won't always need this much control, but it's standard practice in more robust procedures.

Both of the above routines exit the procedure, but sometimes, you'll want to continue executing the procedure -- not exit it. Specifically, Resume returns control to the line that generated the error. Resume Next returns control to the line immediately following the line of code that generated the error. The distinction is important. If your error-handling routine corrected the error, returning to the line that generated the error might be the appropriate action. Then again, skipping that line might be the appropriate action. Exiting an error this way can be complex, so use care and be sure to thoroughly test your routines.