Tip Three good error handling rules to live by are check for the error handle it and proceed accordingly

Returning our attention to the DebugExample01.xlsm file, let's trap for the type mismatch error we got in our first go around with this file. We'll begin by resetting the file as follows:

2. If there is a totals row present, delete it.

3. Save the file.

A type mismatch error is error number 13, and occurs when you try to place a value of one data type into an incompatible data type (in this example, a string into a numeric data type). See Figure 7-27 in our earlier example for an example of a type mismatch error. We are going to modify our code to trap for error 13 and display a friendly message to the user.

Open the VBE by clicking the Visual Basic command on the Code tab of the Developer ribbon, or by pressing Alt+Fll. Open Standard Module2, and find the GetSalesTotal function.

In the GetSalesTotal function, we have a loop (Figure 7-40) that uses a temporary placeholder variable, temp, to hold the running total value of the cells in the range passed in. This variable is defined as a Currency data type. The Currency data type can hold an awfully large numeric value, but it cannot hold a string.

Figure 7-40. The looping structure totals the cells in the temp variable.

There are a couple of things we have to do to set up a procedure for error handling:

1. Turn error handling on (also known as enabling error handling).

2. Add line labels so our code knows where to go when an error condition is fired.

3. Handle the error.

4. Resume code execution at the appropriate location.

Let's modify the GetSalesTotal function and add an error handler.

1. Add a variable declaration after the declaration for temp:

Dim sErrMsg As String

The sErrMsg variable will hold the text of the message we'll show our users should an error occur. The variables should now look like those in Listing 7-11.

Listing 7-11. Variable List for GetSalesTotal

Dim currReturn As Currency Dim cell As Range Dim temp As Currency Dim sErrMsg As String

2. Immediately below the variable declarations, add the following line of code to enable error handling:

On Error GoTo Err_Handle

Here is where we tell the compiler where in our code to go if an error is fired. Err_Handle is a line label that refers to a specific point in our code. We'll add it in just a moment.

3. Add two blank lines between the last two lines of code in the GetSalesTotal function, as follows:

currReturn = temp

GetSalesTotal = currReturn

4. Put your cursor in the second blank line and add the following line label: Exit Function:

5. Insert a blank line above the End Function line and type the following:

Exit Function

The code after the loop should now look like Listing 7-12.

Listing 7-12. Exit_Function Line Label Added currReturn = temp

Exit_Function:

GetSalesTotal = currReturn

Exit Function End Function

So far, with the exception of enabling error handling, our code works just like it did originally. Now let's write code to handle the type mismatch error.

6. Put the insertion point at the end of the Exit Function line of code and press Enter.

7. Type the following line label: Err_Handle:

8. Press Enter.

When we enabled error handling by adding the On Error GoTo statement, we referred it to this label. You can name yours according to your own naming convention. Just be sure the label used at the top of the procedure is the same as that used to name the error handler section of code at the bottom of the procedure.

9. Add the following code at the insertion point:

If Err.Number = 13 Then sErrMsg = "A value in your data may not be numeric. Please check your data" Else sErrMsg = "An unexpected error " & Err.Number & " has occurred" End If

MsgBox sErrMsg, vbOKOnly, "Error" Resume Exit_Function

If an error occurs, the code redirects to the Err_Handle section. Here we placed conditional logic that looks for a specific error number. If we were aware of other error conditions, we could simply add them to the If...Else block or even use a Select Case statement.

Inside the If statement, we are assigning the appropriate error message to the sErrMsg variable based on what error occurred. Then we show the user the message. The last line of the error handler section tells the code where to resume once the error is dealt with. In this case, we're telling it to resume at the line label Exit_Function where we assign an output value to our function.

10. Save the code.

11. In Excel, run the AddSalesTotal Macro.

Our friendly message is displayed to the user informing her of the issue with the data, as shown in Figure 7-41.

Figure 7-41. User-friendly error message 12. Click OK to continue.

As shown in Figure 7-42, there is a small issue with the output from the GetSalesTotal function. It returned a value of 0.

Figure 7-42. Zero value returned from GetSalesTotal function

Let's see what happened. Return to the VBE and look at the GetSalesTotal function (Listing 7-13).

Listing 7-13. GetSalesTotal Function with Error Handling

Function GetSalesTotal(RangeToTotal As Range) As Currency Dim currReturn As Currency Dim cell As Range Dim temp As Currency Dim sErrMsg As String On Error GoTo Err_Handle

For Each cell In RangeToTotal temp = temp + cell.Value Next cell currReturn = temp

Exit_Function:

GetSalesTotal = currReturn Exit Function Err_Handle: If Err.Number = 13 Then sErrMsg = "A value in your data may not be numeric. Please check your data" Else sErrMsg = "An unexpected error " & Err.Number & " has occurred" End If

MsgBox sErrMsg, vbOKOnly, "Error" Resume Exit_Function End Function

0 0

Post a comment