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.
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
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.
|Alt-TO | Menu: Tools » Options » Tab: General, Group: Error Trapping|
- 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.
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
- Debug.Print statements for tracing during development
- Additionally, error handling is supported as part of the builders
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. 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
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.
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
- The actual module that was inserted in the current project when you use the Error handler fragment
- The source module which is automatically inserted in your projects (when you use the Error handler fragment)
The centralized error handling module is stored in \My Documents\VBA Code\Error\basErrorHandle.bas.
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.
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
Else does not get a number.
|Alt-CENA | Menu: Code VBA » Error » Number » Add|
|Alt-CENA | Menu: Code VBA » Error » Number » Remove|
|Alt-CED | Menu: Code VBA » Error » Debug.Print|
Add the variable you want to be written to the Immediate window.