Resuming after an error

In some cases, you simply want your routine to end gracefully when an error occurs. For example, you may display a message describing the error and then exit the procedure. (The EnterSquareRoot5 example uses this technique.) In other cases, you want to recover from the error, if possible.

To recover from an error, you must use a Resume statement. This clears the error condition and lets you continue execution at some location. You can use the Resume statement in three ways, as shown in Table 12-2.

Table 12-2 Using the Resume Statement

Syntax

What It Does

Resume

Execution resumes with the statement that caused the

error. Use this if your error-handling code corrects the

problem and it's okay to continue.

Resume Next

Execution resumes with the statement immediately follow

ing the statement that caused the error. This essentially

ignores the error.

Resume label

Execution resumes at the label you specify.

The following example uses a Resume statement after an error occurs:

Sub EnterSquareRoot6() Dim Num As Variant Dim Msg As String Dim Ans As Integer TryAgain:

' Set up error handling On Error GoTo BadEntry

' Prompt for a value

Num = InputBox("Enter a value") If Num = "" Then Exit Sub

' Insert the square root

ActiveCell.Value = Sqr(Num)

Exit Sub

BadEntry:

Msg = "An error occurred. Try again?" Ans = MsgBox(Msg, vbYesNo) If Ans = vbYes Then Resume TryAgain End Sub

This procedure has another label: TryAgain. If an error occurs, execution continues at the BadEntry label and the code displays the message shown in Figure 12-5. If the user responds by clicking Yes, the Resume statement kicks in and execution jumps back to the TryAgain label. If the user clicks No, the procedure ends.

Figure 12-5:

If an error occurs, the user can decide whether to try again.

w

square root example.xls

□as

A

B

« 1 m 1 m.

F

«

1

1

2

2

•3

3

4

Calculate a square root

S

S

7

Microsoft Excel

ti

g

te

2rror occurred. Try again?

10

11

12

¡Lg iles. 1 No

13

14

15-

s

■t <

> >i [Vniart 1 \Sheet 1 /- | <

1 [>

Remember that the Resume statement clears the error condition before continuing. To see what I mean, try substituting the following statement for the second-to-last statement in the preceding example:

If Ans = vbYes Then GoTo TryAgain

The code, which is available on this book's Web site, doesn't work correctly if you use GoTo instead of Resume. To demonstrate, enter a negative number: You get the error prompt. Click Yes to try again and then enter another negative number. This second error is not trapped because the original error condition was not cleared.

0 0

Post a comment