Debugging the Error Handler

After our error message is displayed, we tell our code to resume at the Exit_Function line label. Since we know the code worked fine with all numeric values in our original example and our error message was displayed successfully upon trapping the error, lets add a breakpoint at the point where we resume execution, as shown in Figure 7-43.

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"

End Function

Figure 7-43. Breakpoint added in error handler

1. Return to Excel.

2. Run the AddSalesTotal macro.

3. Click OK when the error message appears, and the code will go into break mode.

4. Press F8 once to step to the next line of code. The execution point will move into the Exit_Function section.

5. Hold your mouse pointer over the currReturn variable to check its value. As shown in Figure 7-44, it has a 0 value.

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 |currReturn = o| 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"

End Function

Figure 7-44. Checking the value of currReturn 6. Press F5 to let the code run to finish.

Since our loop never finished running, currReturn was never assigned a value. We have a couple of choices on how to handle this. We can show no total in case of an error, or we can show the total of the numeric values.

To show a total and get the loop to finish running, we need to modify the behavior of our Resume statement. The Resume statement has three forms, as shown in Table 7-4.

Table 7-4. The Resume Statement

Statement

Description

Resume

Resumes code execution with the statement that caused the error. If the error was

not handled, it becomes fatal.

Resume Next

Resumes code execution with the statement following the statement that triggered

the error.

Resume Line

Resumes code execution at a line label or number within the procedure containing

the error handler.

Our type mismatch error occurs in our loop, and in order to populate currReturn with a value, we'll need to complete the loop.

1. Remove the breakpoint.

2. In the GetSalesTotal function error handler, change the Resume statement to read as follows:

Resume Next

3. In Excel, run the AddSalesTotal macro again.

4. Click OK when the error message is displayed. The correct total for the numeric values will be displayed, as shown in Figure 7-45.

Figure 7-45. Result of using Resume Next to complete our loop

Our On Error GoTo statement refers to a specific line label to handle errors. If you know that any errors you might encounter in a routine are not going to be fatal and can be skipped, you can use the On Error Resume Next statement.

Listing 7-14 shows a modified version of the GetSalesTotal function that uses On Error Resume Next.

Listing 7-14. GetSalesTotal Function Using On Error Resume Next

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 Resume Next

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

Exit_Function:

GetSalesTotal = currReturn Exit Function End Function

On Error Resume Next essentially turns error trapping off. If there's a chance that another section of your code could throw an error, you can turn error trapping back on by adding an On Error GoTo statement inside your code. Figure 7-46 shows the GetSalesTotal function with error trapping turned off for the loop, but turned back on again for the return variable assignments.

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 Resume Next

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

On Error GoTo Err Handle currReturn = temp

Ex it_Funct ion:

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 Next End Function

Figure 7-46. Error trapping turned on by adding an On Error GoTo statement

Now if an error should occur after the loop runs, we can trap it and handle it appropriately in the error handler.

0 0

Post a comment