Even while building as robust an application as possible, errors inevitably occur, and your application is expected to handle them elegantly. In this section, we examine how to use VB and VBA's error-handling features to do just that, first by examining error handling in subroutines and functions within standard modules (i.e., code modules and form modules that make up a standard EXE or a standard VBA program), and then by examining error handling in code or class modules that are used in an ActiveX DLL, EXE, or OCX project. The reason for making this distinction will become clear as you read through these sections.
To begin, let's look at a couple of templates you can use to add error handling to your procedures:
Private Sub Command1_Click() On Error GoTo Command1_Err Exit Sub Command1_Err: MsgBox Err.Number & vbCrLf & Err.Description, _ vbCritical, "Error!" End Sub
This is error handling at its simplest; when an error occurs, an error message is displayed, and the routine in which the error occurred terminates. The second template is a variation on the same theme, but this time the Resume statement resumes program execution at the Command1_Exit label:
Private Sub Command1_Click() On Error GoTo Command1_Err ... Command1_Exit: Exit Sub Command1_Err: MsgBox Err.Number & vbCrLf & Err.Description, _ vbCritical, "Error!" Resume Command1_Exit End Sub
Finally, here is a slightly more sophisticated error-handling device that automatically reexecutes a bunch of code a given number of times—ideal for situations where a connection may be temporarily unavailable:
Private Sub Command1_Click() On Error GoTo Command1_Err Dim iRetries As Integer '...your code goes here Do_Retry: '...your code goes here Exit Sub Command1_Err: If Err.Number = 12345 And iRetries < 5 Then iRetries = iRetries + 1 Resume Do_Retry Else MsgBox Err.Number & vbCrLf & Err.Description, _ vbCritical, "Error!" End If End Sub
The basic structure of error handling in VB begins with the On Error statement. It diverts program execution in the event of an error, or it switches off error handling in the given procedure.
The On Error statement remains valid while the procedure in which it's defined is in scope, or until another On Error statement is encountered. To explain, let's break this down.
First of all, a procedure is within scope until either an end or exit procedure statement is executed. This means that a procedure is still in scope even when a call is made to another procedure. This has important implications for the On Error statement. For example, let's say you define an error handler in one procedure, and you then call another procedure that doesn't contain an error handler. If an error occurs in the called procedure, the first procedure (and its error handler) is still in scope, so the error is handled by the calling procedure.
The following snippet demonstrates how this works. In this example, an error handler is defined in the Command1 button's Click event handler. A call is made to the FunWithNumbers sub, and the value is passed to it as a parameter. Unfortunately, FunWithNumbers uses this value as the divisor and, since it's illegal to divide by zero, a runtime error is generated. The fun has just gone out of FunWithNumbers, and the function has no error handler to handle the error. However, the Command1_Click event handler is still in scope, so the error is handled by the Command1_Click_Err error handler:
Private Sub Command1_Click() On Error GoTo Command1_Click_Err FunWithNumbers 0 MsgBox "all ok" Exit Sub Command1_Click_Err: MsgBox Err.Number & vbCrLf & Err.Description End Sub Private Sub FunWithNumbers(iVal As Integer) Dim i As Integer i = 1 txtResult.Text = CStr(i / iVal) End Sub
To locate an error handler, the VB call stack is used. Each time a call is made from a procedure, it's added onto the end of the call stack. If an error occurs in a procedure that doesn't have an error handler, VB looks at the previous procedure in the stack, until it finds an error handler. If no error handler is found, a terminal runtime error is generated, and your program hits the dust.
A word of warning: you should take care when using On Error Resume and Resume Next within a procedure that calls another procedure. If the called procedure—like FunWithNumbers—doesn't contain any error handling, execution resumes with the line of code containing (in the case of Resume) or the line of code immediately following (in the case of Resume Next) the call to the procedure in the original calling routine that contains the error handler. This is illustrated in the following code fragment:
Private Sub Command1_Click() On Error Resume Next FunWithNumbers 0 MsgBox "All OK" Exit Sub End Sub Private Sub FunWithNumbers(iVal As Integer) Dim i As Integer i = 1 txtResult.Text = CStr(i / iVal) End Sub
The user would assume that the procedure has worked correctly because all he sees is the "All OK" message; FunWithNumbers hasn't updated the txtResult text box with a value because control didn't return to FunWithNumbers after the error.
An On Error statement, then, remains in effect until the next On Error statement, which can be in the same procedure or in a called procedure. For instance, in the example above, if FunWithNumbers had implemented an error handler, it would have handled the error rather than passing it back up the call stack to the command button's error handler. The following is an example of a procedure that uses multiple On Error statements. In this example, an error handler is defined and immediately activated. However, later in the procedure, a For Each...Next statement is used in a way that most likely will cause an error in normal operation (as would happen, for instance, if a control in the Controls collection doesn't have a Text property), so the original On Error statement is replaced by an On Error Resume Next statement that basically skips past any errors. Once this section of the procedure is complete, the original error handler is switched back on, canceling On Error Resume Next:
Private Function ResetControls() As Boolean On Error GoTo ResetControls_Err Dim oControl As Control If Not blnSaved Then Call ShowWarningMsg Exit Function End If On Error Resume Next For Each oControl In Controls oControl.Text = "" Next On Error GoTo ResetControls_Err Call DisplayDefaultValues Exit Function ResetControls_Err: MsgBox Err.Number & vbCrLf & Err.Description End Function
Note that if an error is generated within an error handler, the error is terminal; Visual Basic doesn't look back up the call stack for another enabled error handler.
The options for error handling within a VB procedure are set using the On Error statement as follows:
On Error 0
Switches off error handling until the next On Error statement (a procedure can contain any number of On Error statements).
On Error Resume
Take care with this one! Program execution continues with the line that caused the error; using it may not be the smartest thing you ever did!
On Error Resume Next
Basically, the error is ignored, and program execution continues with the line following the line containing the error.
On Error Goto label
label is the beginning of your error-handling routine within the procedure. A label is a subroutine name followed by a full colon (:).
The Exit Sub statement typically isn't considered an error-handling statement, though it's present in virtually every error handler. As the templates above illustrate, if you forget to include it before the error handler, program execution always falls through to the error handler, whether or not an error has occurred.
The Err object has the following properties:
Description
A string containing a standard description of the last error.
Number
Source
A string containing the application or ActiveX server name in which the error was generated.
LastDLLErr
About as much use as an ashtray on a motorbike. This supposedly contains the error number from a called DLL—supposedly. And if you actually get it to work, you must remember that the Err object's Description property isn't updated with a description of a DLL error.
HelpFile
A string containing the full path to the help file for this application (if one is available).
HelpContext
A string containing the context ID of the help section relating to this error (if available).
Here are its methods:
Resume as a standalone statement, when not combined with the On Error statement, terminates the error handler and indicates where program flow should return. The Resume statement resets the Err object's properties to their default values, then resumes normal program execution at the point specified by the argument included with the Resume statement. If no arguments are included—i.e., the Resume statement is used on its own—execution recommences with the line of code that generated the error. The valid arguments are: