Debugging Macros

5E it

You can actually use three different forms of the Resume statement within your code: Resume, Resume Next, and Resume Label. You typically place each of these statements at the end of the error-handling portion of your code to indicate how to return to the main portion of the code.

The Resume Next statement returns control to the next line of code after the line that produced the error, thus continuing execution without that line of code. This option enables you to complete the procedure, but typically does not produce the anticipated results due to the skipped code.

The third form of the Resume statement, the

You should use the Resume statement, which returns to the line of code that originally caused the runtime error, with caution. If the line of code executes and produces another error, the error-handing code calls again. Use this statement only when you are sure that you corrected the condition that caused the error. For example, if you instruct the user to enter a valid value in a cell, you can resume execution so that the value is verified again.

Resume Label statement, transfers control to another labeled area of code.

Q Type Resume Next.

-|0|x|

¡If] File Edit

View Insert Format

Tort,

Data Window

Help

| Type a question for help

- . fi>

X

a y

is

il mi

¡al

-II'» "Il B

/ u\m

D21

fx

A

B

c

[1

E

F

G

H

1

J

K

L

1

1

2

2

3

3

4

bad numb

5

6

7

8

9

in

IT

containing a

13

e line or coae

un time eirot.

14

■--

15

1 °>- 1

16

17

18

19

20

21

1

22

23

24

25

26

27

-

M ► H |\Sheet! / 5heet2 / 5heet3

"1 1 JJ

S Type Label:, replacing Label: with the appropriate label name for the error-handling code.

L-0 Type the VBA code to execute if an error occurs.

Q Type Resume Next.

O Switch to Excel and run the macro.

-■ If a runtime error is encountered, the appropriate VBA code executes.

PROCESS A RUNTIME ERROR

You can use the error code that VBA captures from a runtime error to make corrections so that the procedure executes correctly. Whenever VBA encounters an error during the execution of a procedure, it places the error information, which includes the error code and description, in the Err object. You can use this information to process the error and often correct the error situation.

To ensure that you capture the error without halting the execution of your code, place the On Error Resume Next statement immediately after the Sub statement for your subroutine. This statement instructs VBA to capture the error and continue processing.

The Err.Number property contains the error code if a runtime error occurs. The error codes for runtime messages are between 1 and 65,535. Essentially, if the Number property has a value greater than zero, an error occurred. You can quickly check to see if an error exists by checking the Number property of the Err object with an If Then statement as in the following code: If Err.Number >0 Then

The real power of using the Number property comes from the ability to execute different code based upon the error message code return by the runtime error. You can design your error processing code to react differently depending upon the specific runtime error encountered. For example, if the Err.Number property has a value of 13, the value passed to a variable is not the correct data type;for example, you may have specified a string for a variable that required an integer value. If you write code that examines the runtime error, you can prompt for the correct data type.

PROCESS A RUNTIME ERROR

PROCESS A RUNTIME ERROR

—H Create a new subroutine.

0 Type On Error GoTo Label, replacing Label with the label for the code to execute.

—H Use the Dim statement to declare subroutine values.

□ Type VBA code for subroutine.

—H Create a new subroutine.

Note: See Chapter 3 for information on creating subroutines.

0 Type On Error GoTo Label, replacing Label with the label for the code to execute.

—H Use the Dim statement to declare subroutine values.

Note: See Chapter 3 for more information on declaring variables.

□ Type VBA code for subroutine.

0 0

Post a comment