Chapter 23: Handling Errors and Exceptions
833
l (^) VBA procedures often open recordsets, establish object variables, and perform other
tasks that may be left incomplete unless the procedures are shut down in a predict-
able fashion. For instance, assume a procedure has opened a disk file and an error
occurs. Unless the disk file is explicitly closed, you run the risk of damaging the disk’s file
structure. Using the Resume statement to redirect flow to the procedure’s shutdown code
provides a single point at which to close resources that are no longer needed.
Every VBA error handler should include some form of the Resume statement. This special VBA
command instructs the VBA engine to clear the error condition and resume normal execution.
Depending on how you write the Resume statement, you can redirect program execution to any of
a number of different points within the procedure.
Note
The VBA GoTo statement will not work in place of Resume. GoTo is an unconditional branch to another loca-
tion within the current procedure and does not reset the VBA engine error status.
Resume
The Resume statement (with no label) returns execution to the line at which the error occurred.
The Resume statement is typically used when the user must make a correction, or when the error
handler has repaired the problem causing the error. This might occur if you prompt the user for
the name of a file to open and the user enters a filename that doesn’t exist. You can then force the
execution of the code back to the point where the filename is requested.
In almost all cases, the Resume keyword assumes that the error handler repairs the error condi-
tion. Otherwise, you’ll find yourself in an endless loop. Unless the error condition is corrected,
every time the line causing the error is executed, the error occurs again, triggering the Resume
statement, causing the cycle to repeat itself an infinite number of times. The following procedure
shows how the Resume statement fits into a robust error handler and how the Resume statement
can simplify coding:
Public Sub ResumeDemo()
On Error GoTo HandleError
‘Statement causing error occurs here:
Kill “C:\Temp.txt”
ExitHere:
Exit Sub
HandleError:
If MsgBox(“Error! Try again?”, vbYesNo) = vbYes Then
Resume
Else
Resume ExitHere
End If
End Sub
If the Temp.txt file cannot be found, processing jumps down to the error handler. A message
box pops up with Yes and No buttons on it, asking the user whether to try again to delete the file.