If your application doesn’t react the way you intended, or if it produces unexpected results or simply stops working for no apparent reason, then your program probably has what’s known as a bug. The process of hunting down and correcting bugs is called debugging. This topic describes various types of errors you may accidentally introduce into your code. It also discusses the extremely useful features that are available from the VBA IDE to help you identify and fix your problems when things go wrong.
You may encounter the following types of errors in the VBA code you work with:
• Logic errors are made during the development phase as you write the code.
• Compiler errors are sometimes discovered by the VBA interpreter immediately after you enter a statement. Sometimes they are discovered by the compiler program when it tries to translate the code into an executable form.
• Run-time errors occur during execution time.
The VBA IDE doesn’t attempt to fix any of these errors for you, but it does provide useful features to help you when things go wrong. You’ll be able to do as much as possible to ensure that the code you enter is syntactically correct so that the interpreter program can understand it. Other features assist in debugging your code when it doesn’t quite work in the way it’s supposed to.
The next three sections describe these three classifications of errors in more detail.
Logic Errors
Logic errors, introduced as you develop the VBA code, only become obvious when your application doesn’t perform as expected. For example, suppose your application requires the user to enter a telephone number into a text box and, as each key is pressed, validates the corresponding character to ensure that it’s a number. If not, the application rejects it and sounds a beep.
The text box control in your application has a KeyPress event procedure that is passed the numeric ASCII code of the last key pressed as its KeyAscii parameter. The KeyPress event procedure for the telephone number entry in Listing 10.1, for example, is syntactically correct, runs like a dream without giving any error messages, doesn’t fall over—and yet, when you enter a letter into the text box instead of a number, your letter is accepted and appended to the Text property string. Which was not the original idea and isn’t supposed to happen.
The error here is in the condition of the If statement. As written, the condition will always be False because there is no key you can press that produces a character that is both less that the ASCII code for zero and greater than the ASCII code for 9. For this condition to be True, the character input would have to be represented by two different ASCII codes.
To rectify this event procedure, the And from the If statement on Line 2 must be changed to an Or. That way, only numbers will get displayed in the text box and the others will be discarded.
If you want to run this event procedure and examine it as you work through this topic, you’ll need to set up a GUI with a UserForm containing a label and a command button control as shown in Figure 10. Change the Caption properties to those shown in the figure, and update the Name property of your tex\ box to txtTelephone.
Figure 10.1 Input Validation GUI for prompting the user to enter a telephone number
Listing 10.1: KeyPress Event Procedure
Analysis
• Line 1 starts the KeyPress event procedure ofthe txtTelephone text box.This has a ByVal parameter, KeyAscii, that’s assigned the ASCII code value of the character associated with the key just entered.The KeyAscii parameter is passed a reference to the Microsoft Form’s ReturnInteger object, which allows you to reject any invalid characters.
• Line 2 uses the If statement with a condition that attempts to verify that the KeyAscii character is not in the range 0 through 9. It mistakenly uses the And operator, however, instead of the Or operator. The result is that any character entered gets accepted and appended to the text in the text box.
• Line 3 cancels the keypress and is only executed if the condition in the If statement evaluates to True.
• Line 4 sounds a beep to alert the user that the last character input has been rejected.
• Line 4 ends the If statement block.
• Line 5 ends the KeyPress event procedure.
Compiler Errors
Compiler errors occur because you have entered code incorrectly. Some compiler errors are picked up right away when you enter a statement that doesn’t meet the syntax requirements of the Visual Basic language. Other compiler errors are detected during compile time, while your source code is being translated into executable code ready for running by the Compile ACADProject command under the Debug menu.
Getting Feedback as You’re Entering Code
The VBA IDE can be set up to provide immediate feedback as soon as you enter a statement containing a syntax error. For example, if you enter the following statement and press Enter:
If A = B
you’ll see the following message box containing the error message about an expected Then:
If you try this and no message appears, you’ll need to make sure that you have enabled the Auto Syntax Check option in the IDE Options dialog box (Figure 10.2):
1. Choose Tools ^ Options and select the Editor tab.
2. If the Auto Syntax Check option isn’t on, select the check box to enable it.
3. Click OK to return to the VBA IDE.
Figure 10.2 The Editor tab from Tools – Options contains the Auto Syntax Check option.
ByVal Parameters vs. ByRef Parameters
A ByVal parameter is passed a copy of a variable, so if the procedure changes the value of the parameter, only the copy is changed. This means the variable still keeps its original value.
A ByRef parameter is passed a reference to a variable that enables access to the contents in its memory address allocation. Updating a ByRef parameter updates the memory address contents,so the variable will still be set to the new value after the procedure has finished executing.
A ByVal parameter can also be passed a reference to a ReturnBoolean, ReturnEffect, ReturnInteger, or ReturnString object. As such, ByVal acts more like a ByRef parameter, in that you can update the named parameter,and the Value property of the object will retain the new value even after the procedure has finished.
Errors during the Compilation Stage
Following are a couple of examples of errors that often occur as you compile the entire application into executable code. (This is the last step you’ll do prior to running the code.)
• Variables that are not defined even although the Option Explicit statement is included at the start of the module. When the compiler comes across such variables, it displays the message “Compiler error: Variable not defined.”
• Missing arguments from calls to a method or function. When a method or function call does not include all the required arguments, the message “Compiler error: Argument not optional” appears.
Run-Time Errors
A run-time error occurs while the code is being executed and usually means that a statement has attempted to perform some invalid operation.
One common run-time error is when your code tries to divide a number by a variable that’s set to zero.
When you’re working with variables that are supposed to be set up as references to objects, another common run-time error you might make is to forget to include the Set in the assignment statement. For example, the following statement
produces a message box containing the error message Run-time error 91 : Object variable or With block variable not set.” This statement should have been written as follows:
Err Object
The properties of the Err object are assigned the details of a run-time error. They uniquely identify the error, give its source, and provide a description including (if possible) how to handle the error. The following table lists and describes the Err object’s properties. The Err object’s properties are initially set to zero or to empty strings. When an error occurs, they are reset to values and strings that provide useful information about the error.
Number Numerical value in the range 0 to 65,535 that, in combination with the Name property, uniquely identifies the error. This property is used as the argument passed to the Error statement.
Source Returns the name of the object or the application that caused the error. Description Contains an error message from the application or an object. HelpFile Pathname for the Visual Basic Help file.
HelpContext The Help file’s context ID for the error associated with the Number property.
LastDLLError Contains the system error code for the last call to a Dynamic Link Library (DLL).
