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.NOTENote 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 > StatementThe 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.