OK, I could only come up with 7. Maybe others can help me round out the list.
The 10 Commandments of VBA
1. Thou shalt declare thy variables. In the VBA editor, go to Tools > Options and click ON the box that says Require Variable Declaration. This action will add Option Explicit to the top of each module you create. Turn it on and leave it on for the rest of your VBA life.
2. Thou shalt comment thy code. You will be surprised how much you will forget about the code you wrote. Worse yet, if someone has to come back to maintain your code, you might not be there to have them read your mind.
3. Thou shalt tell VB where thee want it to be. Your idea of what the active sheet or active cell is may be different than what Excel thinks they are.
4. Thou shalt indent. The compiler and VB interpreter doesn’t care if the code is indented or not. However indentations will help you keep multiply-nested if statements and loops properly closed.
5. Thou shalt be consistent with thy naming conventions. For example, I use sht as prefix for sheets, shp for shapes, LRow for last row, RowNum for working row number. So if I am working on a sheet call “Data” these get to be shtD, LRowD and RowNumD. Pivot table parts are pt for the pivot table itself, pf for pivot field and pi for pivot item.
6. Thou shalt initialize variables. Some variables must be initialized. Others are self-initializing. For example, strings that are not initialized start out as the null string and uninitialized numbers start out as zero. However there is no harm in explicitly setting these values. Other languages require it.
7. Thou shalt write reusable code. Write subroutines and functions so you can pass parameters to them rather than “hard coding” things in. For example, I have frequent need to clear the contents of Excel tables. I have a save module I can pull in called ModClearTable. In it is a subroutine called ClearTable whose two arguments are a pointer to a sheet (which may even be in another workbook) and the table name.
Bookmarks