Better Error Handling

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.

vba default error message

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.

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 Aptions 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.

Options Error Trapping

Add Error Handling

The Code VBA Error menu

menu showing support for error handling

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

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
    Exit Sub
    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
    Exit Sub
    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 Start demo screencast to see the error handler displaying an error message and having the error reported to you the developer.screencast showing error handling with reporting in action

5. 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 Else
    End Select

6. 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 start change email address demo to see the change in the error handler to set the email address where error reports will be sent to. Two places to chose fromchange email address for error reporting

  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 ErrorHandlecall. If you add line numbers you can even identify in which line the error occurred.

Add / remove line numbers

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.