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
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.
|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.
|A faster way to switch between error trapping options is via the Error menu » Trapping|
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:
- As optional code created by the procedure builder
- From the Code Explorer Right-click menu.
- 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.
|You can adapt the pre-fabricated error handling code fragments, or create new ones using the Fragments Editor (start Edit/Manage Fragments from Code VBA Error menu below). To have it picked up by the Procedure Builder you need to store the fragment under the \Error subfolder and start the name with 'Error handling - '.|
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.
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.
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.
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
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.