Better Error Handling - made easy using Code VBA

Visual Basic automatically adds a form of error handling to your application which is good for developers, but not for end users. If an error occurs during a procedure your app will come with a message with error code and description. Depending on the VBA Settings, even allowing the user to Debug or End - not so nice.

VB default error message. We don't want users to see this!

In the following I show what your error handling options are and how our Code VBA centralised error handler will give you an excellent feedback about errors.

• Add Error Handling
• Error reporting using mail
• Customizing the centralized error handling procedure
• Locating errors module and procedure name strings

Built-in error trapping options

The exact behavior that occurs in case of an unhandled error is set in the Error Trapping on the General tab of the Options form of the Visual Basic Editor (VBE). Note that the chosen behaviour is applied to any VBA project of the current user - is is not restricted to the current project.

• Break on All Errors: Stops on every error, even errors following a Resume Next statement. Good for developers, not for end users.
• Break in Class Module: 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 on Unhandled Errors: Shows error message. After Ok the code of the procedure is aborted. Good for end users.

Add Error Handling using Code VBA

The most basic form of error handling is showing a dialog stating an error has happened and then aborting the macro. To implement this you can use the commonly used code block described under 3. Basic Error Handling below.

Code VBA has three ways in which you can add your choosen error handler fragment to your code:

1. As optional code created by the procedure builder
2. From the Code Explorer Right-click menu.
3. Using the Code VBA Error menu

Due to how the error fragment markup was done the error handling code is distributed over the procedure correctly even if it is not empty.

The Code VBA Error menu

In the Code VBA menu you will find under the 'Error' entry entries to speed up error handling.

• Add error handling
• Add / remove line numbers: used to identify exactly in which line an error occurs when reporting.
• Debug.Print statements for tracing during development
  see also Where does Debug.Print write output?.

When considering error handling we have to match the possible error situations with the available error handling options:

1. Simple property Let/Get situation. - no error handling

Public Property Get ColorText() As String
ColorText = msColorText
End Property

I am quite certain we don't need error handling here. What could go wrong with assigning a string to a string variable?

2. Don't really care - on error resume next

If your code does nice things like changing the status bar or setting the hourglass or disabling controls that are not critical to the main process you may decide in case of error you don't want to bother the user with minor errors.

3. Basic Error Handling

The code below shows a simple example of the use of basic error handling, which in case of error simply displays a message box showing the reason the error failed. Maybe deleting the record was because it had related records that reference it.

Private Sub cmdDeleteRecord_Click()
On Error GoTo HandleError
DoCmd.RunCommand acCmdDeleteRecord
HandleExit:
Exit Sub
HandleError:
MsgBox Err.Description
Resume HandleExit
End Sub

4. Error Handling with reporting by email

This is implemented in the fragment at the first position of the Error sub menu: 'Error handling'. It supports a common technique of transferring control to section with code dedicated to handling the error:

Sub MySub()
On Error GoTo HandleError

HandleExit:
Exit Sub
HandleError:
ErrorHandle Err, Erl()
Resume HandleExit
End Sub

If this is chosen additionally the module basErrorHandle with centralized error handling procedure called ErrorHandle is inserted.

Click this to see the error handler displaying an error message and having the error reported to you the developer.

5. Controlled Sub

By this we mean that the decision on how to handle an error is made not in the procedure in which the error condition occurred, but by a procedure higher up in the call stack. The calling procedure may use info it received from the raised error to decide the most appropriate action.

The code below gives an artificial example of this. MainPlus4 asks for a number to add 4 to and then display the result. The user enter a value via the inputbox. He may enter a number, a text string, or nothing. The calculation procedure SubProcCalcPlus4 only knows if he can successfully add 4 to the input or not. It does not know how to handle errors as he does not know in what context (by which procedure, to what purpose) it is called. In such situation the best is to call Err.Raise with suitable information which will give the responsibility for handling the error back to the calling procedure. In this example the calling procedure decides if the user did not enter a value, this is to be interpreted as the user wanting to abort the calculation, so no further message is needed:

Sub MainPlus4()
On Error GoTo HandleError
Dim str As String
str = InputBox(Prompt:="Give number", Title:="Add 4")
MsgBox SubProcCalcPlus4(str)
HandleExit:
Exit Sub
HandleError:
Select Case Err.Number
Case 13 'Type Mismatch'
If str = "" Then
    Resume HandleExit
Else
    MsgBox Err.Description
    Resume HandleExit
End If
Case Else
MsgBox Err.Description
Resume HandleExit
End Select
End Sub

Function SubProcCalcPlus4(var As Variant) As Long
On Error GoTo HandleError
SubProc = var + 4
Exit Function
HandleError:
Err.Raise Err.Number, IIf(Err.Source = Application.VBE.ActiveVBProject.Name, "Module1.SubProcCalcPlus4" & " " & Erl, Err.Source), Err.Description
End Function

6. I expect an error here! - On Error GoTo HandleSelectCase

The well-known example here is of course trying to read a file. You may only hope it is there. There is a whole set of related and similar errors and handling them is probably something you want to do in the procedure in which the error occurs (e.g. retry/cancel), not in the centralized error handler.

Select Case Err.Number
Case

Case Else

End Select

7. No, I want to handle differently! - Do it yourself

OK, you probably know a better way. No problem, you can create your own error handling fragment using the Fragment Editor.

Error reporting using mail

The best way to have errors handled is allowing users send you an email when the error occurs, automatically, with all relevant details (what error number, which procedure and line, additional info) included. This type of automated feedback let's you know how well your programs behave with minimum effort. You will get this reporting behaviour whenever you use the error handling.

Click this to see the change in the error handler to set the email address where error reports will be sent to. Two places to chose from

1. The actual module that was inserted in the current project when you use the Error handler fragment
2. The source module which is automatically inserted in your projects (when you use the Error handler fragment)

Customizing the centralized error handling procedure

The centralized error handling module is stored in \My Documents\VBA Code\Error\basErrorHandle.bas.

Locating errors module and procedure name strings

It is a common practice to make available not only the error info, but also where the error occurred. For this automatically the Error handler fragment automatically adds Module and Procedure name strings in the ErrorHandle call. If you add line numbers you can even identify in which line the error occurred.

Add / remove line numbers

You can add line numbers using either the Code VBA menu, or using the Code Explorer

Add line numbers using the Code VBA menu

To add line numbers you first have to select the lines you want to have them added to. Only lines where an error can occur actually get a number. As an example, a line Next or Else does not get a number.

Add debug statements

Add the variable you want to be written to the Immediate window.