Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources]

Using Resume Statements

While you are in your error-handling code, you can use the Resume, Resume Next, and Resume <LineLabel> statements to specify how you want VBA to respond to the error. Resume attempts to re-execute the offending line of code, Resume Next resumes execution after the offending line of code, and Resume <

LineLabel > continues execution at a specified line label. The following sections cover these statements in detail.

The Resume Statement

The Resume statement resumes code execution on the line of code that caused the error. You must use this statement with extreme care, because it can throw the code into an unrecoverable endless loop. Listing 16.5 shows an example of an inappropriate use of the Resume statement.

Listing 16.5 Using Resume Inappropriately

Function BadResume(strFileName As String) 'Invoke error handling On Error GoTo BadResume_Err Dim strFile As String 'Perform the Dir function to determine if 'the file passed as a parameter exists strFile = Dir(strFileName) 'If the file doesn't exist, return False 'Otherwise, return True If strFile = " Then BadResume = False Else BadResume = True End If 'Exit the function if all goes well Exit Function BadResume_Err: 'Display an error message with the 'description of the error that occurred MsgBox Error.Description 'Attempt to re-execute the offending line of code Resume End Function

This function is passed a filename. The Dir function searches for a file with that name and returns True or False, depending on whether the specified file is found.

The problem occurs when the drive requested is not available or does not exist. This code throws the computer into an endless loop. To remedy the problem, you should modify your code to look like the code in Listing 16.6.

Listing 16.6 Using Resume Conditionally Based on User Feedback

Function GoodResume(strFileName As String) 'Invoke error handling On Error GoTo GoodResume_Err Dim strFile As String 'Perform the Dir function to determine if 'the file passed as a parameter exists strFile = Dir(strFileName) 'If the file doesn't exist, return False 'Otherwise, return True If strFile = " Then GoodResume = False Else GoodResume = True End If 'Exit the function if all goes well Exit Function GoodResume_Err: Dim intAnswer As Integer 'Ask user if they want to try again intAnswer = MsgBox(Error & ", Would You Like to Try Again?", vbYesNo) 'If they respond yes, attempt to re-execute the offending line 'of code. Otherwise, exit the function If intAnswer = vbYes Then Resume Else Exit Function End If End Function

In this example, the error handler enables the user to decide whether to try again. The Resume occurs only if the user's response is affirmative.

The Resume Next Statement

Just as you can invoke error handling using an On Error Resume Next statement, you can place a Resume Next statement in your error handler, as Listing 16.7 shows.

Listing 16.7 Placing a Resume Next Statement in Your Error Handler

Sub TestResumeNextInError() 'Invoke error handling On Error GoTo TestResumeNextInError_Err 'Attempt to delete a file Kill "AnyFile" 'If no error occurred, do nothing. Otherwise, display 'a message with the description of the error that occurred If Err.Number = 0 Then Else MsgBox "We Didn't Die, But the Error Was: " & Err.Description End If Exit Sub TestResumeNextInError_Err: 'Reset error information and resume execution on the 'line of code following the line on which the error cocurred Resume Next End Sub

In this example, the code is instructed to go to the label called TestResumeNextInError_Err when an error occurs. The TestResumeNextInError_Err label issues a Resume Next statement. This clears the error and causes execution to continue on the line after the line on which the error occurred. The MsgBox therefore never displays.

NOTE

Note the difference between On Error Resume Next and Resume Next. You place On Error Resume Next in the body of the routine. It causes code execution to continue on the line of code following the line that caused the error. It does

not reset any error information.

You place Resume Next

within the error handler. It also causes code execution to continue on the line of code following the line that caused the error. It

does reset the error information.

The Resume <

LineLabel > Statement

The Resume <

LineLabel > statement enables you to specify a line of code where you want code execution to continue after an error occurs. This is a great way to eliminate the two Exit Sub or Exit Function statements required by the error-handling routines you have looked at so far. Listing 16.8 shows an example.

Listing 16.8 Using the Resume <LineLabel> Statement to Specify Where Execution Continues After an Error Occurs

Sub TestResumeLineLabel(intVar1 As Integer, intVar2 As Integer) 'Invoke error handling On Error GoTo TestResumeLineLabel_Err Dim sngResult As Single 'Divide the value received as the first parameter 'by the value received as the second parameter sngResult = intVar1 / intVar2 TestResumeLineLabel_Exit: 'Exit subroutine Exit Sub TestResumeLineLabel_Err: 'If an error occurs, display message with the error 'number and description MsgBox "Error #" & Err.Number & ": " & Err.Description 'Resume execution at the TestResumeLineLabel_Exit label Resume TestResumeLineLabel_Exit End Sub

Notice that this routine contains only one Exit Sub statement. If no error occurs, Access drops through the TestResumeLineLabel_Exit label to the Exit Sub statement. If an error

does occur, the code in the TestResumeLineLabel_Err label executes. Notice that the last line of the error label resumes execution at the TestResumeLineLabel_Exit label.

This method of resolving an error is useful because any code required to execute as the routine is exited can be written in one place. Object variables might need to be set equal to Nothing as the routine is exited, for example. You can place these lines of code in the exit routine.