Error Handling
Introduction
"Anything that can go wrong - will go wrong."
Good error handling is essential to good programming. Things will go wrong and our code must not break when they do. In general, we should avoid letting errors happen; but when they do, we should never simply ignore them. Though VBA allows us to do both with its On Error Resume Next statement, in my humble opinion, that is a dangerous practice and an indicator of the coder’s attitude and skill. With the methods presented here we can handle errors far more elegantly and with very little effort. In addition, the technique presented here can provide help text and, when in the hands of the developer, aid in debugging.
Error Handling Goals
The purpose of error handling, besides saving our careers, is to:
Prevent dumping users into code
Alert users that something went wrong and, if possible, guide them on how to fix things or contact developers for code corrections
Bring the program to a stable point so users can continue working.
Coding Best Practices
I love the method I’m going to show you. It saves me so much time and frustration. Even if error handling wasn’t a best practice, I’d eagerly copy these lines into every procedure I code. Fortunately, with the template provided here, we can incorporate this best practice into new routines with almost no effort. And when ‘retrofitting’ old routines, copying this error handling into them takes just a few seconds
Error Handling Flow
Near the top of every routine, we should initialize error handling with this code:
On Error GoTo ErrHandler
This enables error handling. Should an error occur in our code, this statement will cause our procedure to jump to the ErrHandler: tag where our error handling routine sits at the bottom of our procedures:
ErrHandler: 'Error Handling, Clean Up and Routine Termination
Select Case Err.Number
Case Is = NoError: 'No error - do nothing
Case is = #: 'Add specific error handling here
Case Is = RtnError: Template = CVErr(xlErrDiv0) 'Return Error code to spreadsheet
Case Is = LogError: Debug.Print cRoutine, Err.Description 'Log the event and go on
Case Else:
Select Case DspErrMsg(cModule & "." & cRoutine)
Case Is = vbAbort: Stop: Resume 'Debug mode
Case Is = vbRetry: Resume 'Try again
Case Is = vbIgnore: 'End routine
End Select
End Select
The above ErrHandler: includes more lines than is typically necessary. Below is its more common form.
ErrHandler: 'Error Handling, Clean Up and Routine Termination
Select Case Err.Number
Case Is = NoError: 'No error - do nothing
Case Else:
Select Case DspErrMsg(cModule & "." & cRoutine)
Case Is = vbAbort: Stop: Resume 'Debug mode
Case Is = vbRetry: Resume 'Try again
Case Is = vbIgnore: 'End routine
End Select
End Select
Error Handling Constants and Variables
To better document the code, I use named constants and variables to control program flow. We can see these constants and variables in the ErrHandler: block. I declare these constants and variables in my general purpose library: modGeneral and nowhere else (some exceptions apply) as shown below.
'Global Constants
Global Const Success As Boolean = False
Global Const Failure As Boolean = True
Global Const NoError As Long = 0 'No Error
Global Const LogError As Long = 997 'Log Error
Global Const RtnError As Long = 998 'Return Error
Global Const DspError As Long = 999 'Display Error
'Global Variables
Public bLogOnly As Boolean 'See DspErrMsg
Public bDebug As Boolean 'See DspErrMsg
The Err Object
The ErrHandler: block relies on the VBA's Err object. Every error sets VBA's Err object. The Err object has several properties. One is the error number (Err.Number). If there has been no error, Err.Number will be 0 which matches the global constant NoError. If Err.Number equals NoError then control passes to the End Select at the bottom of the error handling block.
If Err.Number is greater than zero, an error has occurred. If needed, we can code for specific error codes by changing the # in Case is = #: to the error number we are expecting and then handle as we see fit. We can add as many of these specific error lines as we desire.
It is impractical to check for all errors so after an error has occurred and we have checked for specific errors, control passes Case Else. Here, the error handling block passes the module's name and procedure's name to DspErrMsg().
DspErrMsg() is my central error processing routine. It checks VBA's err object and configures a message box according to the presence of a help file in the err object and how we set global variable, bDebug (declared in our general purpose library).
Example
This is a routine template showing how all the pieces fit in a typical routine. And if you would like more information about this template click here.
Function Template(ByVal MyParameter as String) As Variant
' Description:Procedure Description
' Inputs: MyParameter Describe its purpose
' Outputs: me Success: <return this>
' Failure: <return this>
' Requisites Routines ModuleName.Procedure Name
' Classes Class Module Name
' Forms User Form Name
' Tables Table Name (etc.)
' Notes <add if needed>
' Example: ?Template(MyParameter)
' Date Ini Modification
' 01/01/01 CWH Initial Programming
' Declarations
Const cRoutine as String = ‘Template’ 'Routine’s Name
' Error Handling Initialization
On Error GoTo ErrHandler
Template = Failure 'Assume failure
' Check Inputs and Requisites
If sParameter = vbNullString then Err.Raise DspError, , "Parameter missing"
' Initialize Variables
' Procedure
Template = Success 'Successful finish
ErrHandler: 'Error Handling, Clean Up and Routine Termination
Select Case Err.Number
Case Is = NoError: 'No error - do nothing
Case is = #: 'Add specific error handling here
Case Is = RtnError: Template = CVErr(xlErrDiv0) 'Return Error code to spreadsheet
Case Is = LogError: Debug.Print cRoutine, Err.Description 'Log the event and go on
Case Else:
Select Case DspErrMsg(cModule & "." & cRoutine)
Case Is = vbAbort: Stop: Resume 'Debug mode
Case Is = vbRetry: Resume 'Try again
Case Is = vbIgnore: 'End routine
End Select
End Select
' Clean Up
End Function
bDebug
If bDebug is set to TRUE, meaning we want to debug our project, the message box will include buttons: Abort, Retry, and Ignore.
NOTE!
Do not set bDebug to TRUE when given to end users!
Abort drops us into the VBE where we can use F8 to step through the code to see exactly which line failed.
Retry attempts the failing line again. Click Retry after fixing an obvious mistake.
Ignore ends the procedure and returns control to the calling procedure.
If bDebug is set to FALSE, which is how it should be when we deliver our projects to clients, the message box will include only buttons: OK and Help. Help will only appear if the err object contains a reference to a help file.
Raising our Own Errors
We can raise our own errors to alert clients, or us (developers) when something required is missing. Below illustrates a common usage that checks if a table exists in the active workbook before proceeding. If it is missing, it displays a message of our own making.
If Exists(Tables(ActiveWorkbook), "MyTable", oLo) Then Err.Raise DspError, , _
"Problem:" & vbTab & "MyTable not found in " & ActiveWorkbook.Name & vbLf & _
"Solution:" & vbTab & "Click Load Tables to add MyTable"
NOTE!
This example uses the global variable DspError declared in my general purpose library.
UDF (User Defined Function) Errors
VBA can be used to create UDFs. UDF errors must be displayed in the cell containing the UDF. For this purpose I created RtnError to use CVErr(). Set CVErr() to one of Excel's cell error constants to have the cell display the associated error when an error occurrs.
Additional Resources
DspErrMsg()
Template
Err Object