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. After 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. 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.
• It makes your procedures easier to understand.