Error Handling


Now that you are familiar with various types of errors that can occur in your VBA code and how to debug them, it’s time to look at how to write error-handling code in your procedures so that your application performs well for users.

Default Error Messages

You have all used applications that did not have very good error handling and found yourself getting booted out of the application without warning or faced with unfriendly messages like the ones you saw earlier in this chapter. You do not want such problems to happen when users interact with your application. Errors will always occur, but if you design error handlers correctly, at least they can be handled reasonably well.

Handling Errors with an On Error Statement

Errors can be handled in VBA procedures using the On Error statement, as I show you next.

On Error Statement

The On Error statement can be placed on your procedure to specify a section in the procedure to which the execution should jump when an error occurs. Here is an example:

 Sub Test() On Error GoTo HandleError 'normal code for the procedure goes hereExit Sub HandleError: 'code for handling the error goes here Exit Sub End Sub 

Tip 

If you want to make sure your own error handler doesn’t kick in when you’re debugging your code, select Tools image from book Options image from book General and enable the Break On All Errors option. This will allow you to see the root of the error and not your own error handler.

Resume Statement

The Resume and Resume Next statements can be used with the On Error statement. The Resume statement will cause code execution to resume at the line that caused the error, and the Resume Next statement will cause code execution to resume at the line following the one that caused the error. Resume statements are commonly added within the error-handler routines, such as the one shown here.

 Sub Test() On Error GoTo HandleError 'normal code for the procedure goes hereExit Sub HandleError: 'code for handling the error goes here Resume Next Exit Sub End Sub 

Try It Out-Creating an Error Handler

image from book

It is your turn to try your hand at creating an error handler.

  1. Place the following code for a new procedure called TestError in the modBusinessLogic standard module:

      Sub TestError(intValue1 As Integer, intValue2 As Integer) On Error GoTo HandleError     'declare variable to store result      Dim intResult As Integer     'calculate result by dividing first value by second value     intResult = intValue1 / intValue2 Exit Sub HandleError:     MsgBox "An error has occurred in your application: " & Err.Description     Exit Sub End Sub 

  2. Run the TestError procedure from the Immediate Window, as shown in Figure 2-41.

    image from book
    Figure 2-41

  3. The following error message (shown in Figure 2-42) is displayed.

    image from book
    Figure 2-42

How It Works

The TestError procedure accepts two variables as parameters.

  Sub TestError(intValue1 As Integer, intValue2 As Integer) 

The On Error statement indicates that the HandleError section of code should execute when an error occurs.

  On Error GoTo HandleError 

A variable is declared that will be used to store the result of an upcoming mathematical calculation.

  'declare variable to store result  Dim intResult As Integer 

The intValue1 value is then divided by the intValue2 value.

  'calculate result by dividing first value by second value  intResult = intValue1 / intValue2 

Because the intValue2 value you specified when calling the procedure was set to 0, a divide by 0 error occurs when the preceding line of code executes. Thus, the execution of code then jumps to the error handler HandleError at the bottom of the procedure.

 HandleError:     MsgBox "An error has occurred in your application: " & Err.Description     Exit Sub     End Sub 

A message box is then displayed to indicate that an error has occurred and to provide a description of the error.

image from book

The Err Object

The Err object contains information about runtime errors that occur when your code executes. Error handlers can use it to provide useful information to users or to the system to help determine what action should be taken. For example, in the error-handling example you just looked at, the Description property of the Err object (Err.Description) was used to include a description of the error to the user.

The Err object has a global scope. You need not declare it. Some examples of the properties available for the Err object include Number, Description, HelpContext, HelpFile, and Source. Number and Description are the most commonly used properties of the Err object.

Raising an Error

The Err object has a Raise method that allows runtime errors to be raised when necessary. Here is the syntax:

  Err.Raise number, source, description, helpfile, helpcontext 

The number argument is required for raising an error, but the other arguments are optional. Here’s an example to illustrate how you can raise an error in your code.

  Sub TestErrRaise(intValue1 As Integer, intValue2 As Integer)     On Error GoTo HandleError     'declare variable to store result     Dim intResult As Integer     If intValue2 <> 0 Then         'calculate result by dividing first value by second value         intResult = intValue1 / intValue2     ElseIf intValue2 = 0 Then         'raise a custom divide by 0 error         Err.Raise vbObjectError + 513, "TestErrRaise", _                        "The second value cannot be 0."     End If Exit Sub HandleError:     MsgBox "An error has occurred in your application: " & Err.Description     Exit Sub End Sub 

When the error is raised, the code then jumps to the error handler just as if the error had occurred in the traditional fashion.

Using the Errors Collection

The Errors collection stores the most recent ActiveX Data Objects (ADO) database errors that have occurred. Chapter 5 covers ADO in detail. For now, just be aware that the Errors collection can be used to loop through each error that was generated by one database operation. Only a few instances require that you implement such an error-handling feature.

Creating a Generic Error Handler

One way of handling errors is to create a generic error handler that gets called from every sub procedure or function.

Try It Out-Creating a Generic Error Handler

image from book

Create a generic error handler now.

  1. Add the GeneralErrorHandler procedure that follows to the modBusinessLogic standard module:

      Public Sub GeneralErrorHandler(lngErrNumber As Long, strErrDesc As String, strModuleSource As String, strProcedureSource As String) Dim strMessage As String 'build the error message string from the parameters passed in strMessage = "An error has occurred in the application." strMessage = strMessage & vbCrLf & "Error Number: " & lngErrNumber strMessage = strMessage & vbCrLf & "Error Description: " & strErrDesc strMessage = strMessage & vbCrLf & "Module Source: " & strModuleSource strMessage = strMessage & vbCrLf & "Procedure Source: " & strProcedureSource 'display the message to the user MsgBox strMessage, vbCritical End Sub 

  1. Add the procedure TestError2 to the modBusinessLogic standard module:

      Sub TestError2(intValue1 As Integer, intValue2 As Integer) On Error GoTo HandleError     'declare variable to store result     Dim intResult As Integer     'calculate result by dividing first value by second value     intResult = intValue1 / intValue2 Exit Sub HandleError:     GeneralErrorHandler Err.Number, Err.Description, "modBusinessLogic", _                  "TestError2"     Exit Sub End Sub 

  2. Call the TestError2 procedure from the Immediate Window with the values shown in Figure 2-43.

    image from book
    Figure 2-43

  3. The message box shown in Figure 2-44 is displayed.

    image from book
    Figure 2-44

How It Works

The TestError2 procedure operates nearly identically to the TestError procedure. TestError2 has two parameter variables.

  Sub TestError2(intValue1 As Integer, intValue2 As Integer) 

An On Error statement specifies that the HandleError section of code should be executed when an error occurs.

  On Error GoTo HandleError 

A variable is declared to store the result of the division, and then the values that were passed in as parameters to the procedure are used for the division. Because the values passed in cause a divide by 0 error, the code execution skips down to the HandleError section of code.

  'declare variable to store result Dim intResult As Integer 'calculate result by dividing first value by second value intResult = intValue1 / intValue2 

This time, the HandleError section includes a call to the GeneralErrorHandler procedure you just added. The error number, error description, module name, and procedure name are passed as parameters to the GeneralErrorHandler procedure.

 HandleError:    GeneralErrorHandler Err.Number, Err.Description, "modBusinessLogic", _                "TestError2" 

Now let’s turn to the GeneralErrorHandler procedure, since it runs next to handle the error. The GeneralErrorHandler procedure accepts the Error Number, Error Description, Module Source, and Procedure Source as parameters.

  Public Sub GeneralErrorHandler(lngErrNumber As Long, strErrDesc As String, strModuleSource As String, strProcedureSource As String) 

A variable is declared to store the error message, and the error message is created based on the various values passed in to the procedure.

 Dim strMessage As String 'build the error message string from the parameters passed in strMessage = "An error has occurred in the application." strMessage = strMessage & vbCrLf & "Error Number: " & lngErrNumber strMessage = strMessage & vbCrLf & "Error Description: " & strErrDesc strMessage = strMessage & vbCrLf & "Module Source: " & strModuleSource strMessage = strMessage & vbCrLf & "Procedure Source: " & strProcedureSource 

The error message is then displayed to the user:

  'display the message to the user  MsgBox strMessage, vbCritical 

This generic error handler can be called from every procedure you write, changing only those variables that specify the current module and procedure. Errors can also be logged to a table, written to a file, or e-mailed, but those options are beyond the scope of this book.

image from book




Beginning Access 2007 VBA
Beginning Access 2007 VBA
ISBN: 0470046848
EAN: 2147483647
Year: 2004
Pages: 143

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net