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.
It helps if VBA's automatic indentation feature is enabled. To check this, choose Tools, Options to display the Options dialog box and, in the Editor tab, activate the Auto Indent check box.
By default,VBA moves the insertion point four spaces to the right when you press the Tab key.You can change this default by typing a new value in the Tab Width text box in the Editor tab of the Options dialog 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.
Require Variable Declarations
To avoid errors caused 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
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.
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.