The Resume Statement

It is also possible to include the Resume statement in the error-handling portion of the code. This will cause VBA to resume execution at the line that follows the one that caused the error. Thus, the previous code is equivalent to the following:

Sub example()

On Error GoTo ERR_EXAMPLE MsgBox Selection.Cells.Count Exit Sub

ERR_EXAMPLE:

MsgBox Err.Description, vbCritical Resume Next End Sub

There are three variations on the Resume statement:

• Resume ALabel

The first version will cause VBA to resume with the line that caused the error. This is useful if your error-handling code actually repairs the error condition and you want the line that caused the original error to be executed again.

To illustrate, the procedure in Example 7-2 is designed to open a workbook named a:\test.xls. If it does not exist, an error will occur. The error-handling code gives the user a chance to enter a new workbook name, in which case we want to execute the Open method again. Hence the use of the Resume statement.

Example 7-2. Error Handling with the Resume Statement

Sub test()

Dim sNew As String sNew = "a:\test.xls"

On Error GoTo ERR_DISK Workbooks.Open sNew Exit Sub

ERR_DISK:

If Err.Number = 1004 Then sNew = InputBox("Cannot find file. Enter new location or leave blank to

Resume Else

Exit Sub End If End If End Sub

The third variation:

Resume ALabel causes VBA to resume execution at the line labeled ALabel.

0 0

Post a comment