Debugging Macros


You encounter runtime errors as your code executes. As with the other types of errors, when the Visual Basic Editor encounters an error, a message box displays with a description of the error. Also, when the Editor encounters a runtime error, if you have not placed any error handling in the code, execution of the procedure stops.

You typically receive errors when you pass invalid data to your procedure, such as when you pass the wrong data type value to a variable. If you pass a string to an expression that expects a numeric value, a runtime error occurs.

To avoid having your code stop due to a runtime error, you need to use the On Error Resume Next statement at the beginning of your procedure. The statement causes VBA to skip over the error and continue processing the procedure. Although the code no longer halts abruptly due to an error, the error still exists, and you need to handle it. VBA places the information for the error in the Err object. As you write your code, you should check the Err object to ensure that runtime errors do not occur. If you encounter an error, you need to write code to resolve the error situation as quickly as possible.

The VBA Object Model includes the Err object that captures information about a runtime error. You can use the properties of the Err object to capture a runtime error and return a message providing

information on how to resolve the error situation without abruptly halting the procedure. The following table lists the three different properties of the Err object and their descriptions.




Contains a VBA description of the runtime error


Contains the VBA error number of the runtime error


Indicates the name of the current procedure that caused the error

You can create code similar to the following that executes if VBA encounters an error. This code creates a message box containing the description of the error and places the VBA error number in the title bar of the message box. See Chapter 7 for more information on working with message boxes. The generated code appears as follows:


If Err.Number

<> 0 Then

MsgBox Err.

Description, vbCritical, "Error # "

1 &


End If

Unlike the other types of errors, logical errors do not produce any type of error message. Instead a logical error returns unexpected results. For example, although the following code is syntactically correct, it has a logical error in calculating the sales tax. The sales tax rate is 7.5 percent, but the code is charging 75 percent because of a misplacement of the decimal point:

Simple logical errors such as mistyping a value or placing a decimal in the wrong place are sometimes the most difficult errors to spot. Because logical errors normally are not obvious, you typically need to use break points or step throughout the code to find the location of the error. As you step through your code, you can monitor the value of each variable to determine when the value changes to something unexpected. See the sections "Insert a Break Point in a Procedure", "Step Through a Procedure", and "Using Watch Expressions to Debug a Procedure" for more information on inserting a break point and stepping through a macro.


Price1 = 4.


Price2 = 6.


TotalCost =

; (Price1 + Price2) * 1.75

0 0

Post a comment