| Debugging your procedures can be a frustrating job, even during the best of times. Here are a few tips to keep in mind when tracking down programming problems. Indent Your Code for Readability VBA code is immeasurably more readable when you indent your control structures. Readable code is that much easier to trace and decipher, so your debugging efforts have one less hurdle to negotiate. Indenting code is a simple matter of pressing Tab an appropriate number of times at the beginning of a statement.  | By default, VBA moves the insertion point four spaces to the right when you press the Tab key. You can change this default by entering a new value in the Tab Width text box in the Editor tab of the Options dialog box. |
It helps if VBA's automatic indentation feature is enabled. To check this, select Tools, Options to display the Options dialog box and, in the Editor tab, activate the Auto Indent check box. Turn on Syntax Checking VBA's automatic syntax checking is a real time-saver. To make sure this option is turned on, activate the Auto Syntax Check check box in the Editor tab of the Options dialog box. tip  | To have VBA include the Option Explicit statement in every new module, activate the Require Variable Declaration check box in the Editor tab of the Options dialog box. |
Require Variable Declarations To avoid errorscaused by using variables improperly, you should always declare your procedure variables . To make VBA display an error if you don't declare a variable, add the following statement to the top of the module: Option Explicit Break Down Complex Procedures Don't try to solve all your problems at once. If you have a large procedure that isn't working right, test it in small chunks to try to narrow down the problem. To test a piece of a procedure, add an Exit Sub statement after the last line of the code you want to test. Enter VBA Keywords in Lowercase If you always enter keywords in lowercase letters , you can easily detect a problem when you see that VBA doesn't change the word to its normal case when you move the cursor off the line. Comment Out Problem Statements If a particular statement is giving you problems, you can temporarily deactivate it by placing an apostrophe at the beginning of the line. This tells VBA to treat the line as a comment. Don't forget that VBA has a handy Comment Block feature that will comment out multiple statements at once. To use this feature, select the statements you want to work with and then click the Comment Block button on the Edit toolbar. Break Up Long Statements One of the most complicated aspects of procedure debugging is making sense out of long statements ( especially formulas). The Immediate window can help (you can use it to print parts of the statement), but it's usually best to keep your statements as short as possible. Once you get things working properly, you can often recombine statements for more efficient code. Use Excel's Range Names Whenever Possible In Excel, procedures are much easier to read and debug if you use range names in place of cell references. Not only is a name such as Expenses!Summary more comprehensible than Expenses!A1:F10, it's safer, too. If you add rows or columns to the Summary range, the name's reference changes as well. With cell addresses, you have to adjust the references yourself. Take Advantage of User -Defined Constants If your procedure uses constant values in several different statements, you can give yourself one less debugging chore by creating a user-defined constant for the value (see Chapter 3, "Understanding Program Variables"). This gives you three important advantages: -
It ensures that you don't enter the wrong value in a statement. -
It's easier to change the value because you have to change only the constant declaration. -
Your procedures will be easier to understand. The Absolute Minimum This chapter showed you how to use VBA's error trapping and debugging facilities to weed out errors in your code. You began by learning how to lay traps for those pesky errors that are inevitable in any reasonably- sized program. In particular, you learned the particulars of a four-pronged error-trapping strategy: set the trap with On Error GoTo ; code the error handler; use one of the Resume statements to continue the program; and disable the trap (if necessary) with On Error GoTo 0 . You also learned about the properties and methods of the Err object, including the useful Number and Description properties. The rest of the chapter covered the art of debugging your procedures. You learned a basic debugging strategy, which involved determining what kind of error occurred ”syntax, compile, runtime, or logic ”and then either responding to the error message or using break mode to investigate the problem. You learned how to pause and step through a procedure, and how to use breakpoints, watch expressions, and the Immediate window. You closed the chapter with a few debugging tips. Here are some places to go to find related information: -
The best way to stamp out bugs in your VBA code is to become familiar with the basic building blocks of VBA programming. These basics were covered in Part 1, "Getting Started with the VBA." -
An important part of trapping errors is letting the user know what's happening and, possibly, giving her some kind of clue how to fix the problem. Basic user interaction techniques are indispensable here, and I showed you quite a few in Chapter 12, "Interacting with the User." -
To help avoid errors caused by your users, you should set up your application's interface to minimize the possibility of improper data entry and to make it as easy as possible for users to work with the program. Creating a custom user interface is the subject of Chapter 13, "Creating Custom VBA Dialog Boxes," and Chapter 14, "Creating Custom Menus and Toolbars." |
|
