Excel/VBA Golden Rules. These Should NOT Be Optional

[Dave Hawley]

GOLDEN RULES
Also See Excel Best Practices

1. Never Use Manual Calculation Mode, It's a False Reading Waiting to Happen! If You are Forced to use Manual Calculation, you HAVE a Bad Spreadsheet Design Which Should be Fixed, not Catered to.
2. 1 Worksheet For ALL Related RAW Data.
3. Classic Table Format For Related Data. That is, Row 1 For Headings and Corresponding Data Underneath.
4. Don't Mix Raw Data With Final Results, Reports or Data.
5. No Blank Cells in a Table.
6. No Merged Cells (Use Center Across Selection Instead).
7. Avoid Empty Text ("") For Formula Results, Use Zero Instead. Tools>Options - View - Zero Values to Hide Globally. Or, Custom Format Like: 0;-0; To Hide Cell-By-Cell.
8. Avoid Array Formulae and Multiple Criteria SUMPRODUCT. Make Use Of PivotTables and/or Database Functions.
9. Don't Nest 2 Lookups To Avoid #N/A! Allow it to Occur and Reference Like: =IF(ISNA(A1),0,A1) and Hide the Lookup Column
10. Avoid Deleting Rows and Make Use of Auto Filter (AutoFilter), AdvancedFilter or Sort.
11. Avoid Volatile Formulae (especially for current date and/or time. NOW, TODAY etc). E.g If Current Date is Needed in Multiple Formulae, Add =TODAY() Into a Cell You Have Named Today. Then, in Your Formulae use Today in Place of TODAY().
12. Avoid Formatting Cells as Text. Very Rarely is a Text Format Needed.
13. Never Store Numbers as Text.
14. Avoid Changing the Default Horizontal Alignment of Cells. Numbers, by Default are Right Aligned, While Text, by Default, is Left Aligned.
15. Always use True Dates & Times That Excel Will Recognize as Such. Even Headings for Things Like Month & Day Names. Use True Dates and Custom Format as MMMM or DDDD.

Excel Formula Errors


VBA CODE GOLDEN RULES

1. Turn on Option Explicit. Tools>Options - Editor - Require variable declaration in the VBE. Then Educate Yourself on Their Proper Use and Data Types.
2. Split Procedures Into Logical Seperate Procedures and use Call or Run When Needed.
3. Make Good use Of Functions so They Can be Called When Needed and With Variable Elements Passed.
4. Try & Keep all Related Procedures in the Same Module. Excel Compiles Each Module as a Procedure in the Module in Run.
5. You Rarely Need to Select or Activate any Objects to Change Their Properties or Access Their Methods. If you MUST Select a Range Object, Use GoTo.
6. Use a Sheet CodeName Over The Tab Name or Index Number.
7. Avoid Loops. Make Good use of Much Faster Alternatives Like Find (Find Method), AutoFilter, AdvancedFilter, SpecialCells etc.
8. Loops Through Object Collections are the Fastest, Compared to Other Loop Types.
9. Don't Assume Code is Needed. Often a Worksheet Function is FAR better and More Efficient.
10. Avoid Using Custom Functions When a Built-in Worksheet Function can be Used. Even Deeply Nested Worksheet Function are Often a Lot More Effiecient Than Custom Function Written in VBA.
11. Avoid Macros That Delete Rows/Column/Cells. Make Use of AutoFilter, AdvancedFilter or Sort.
12. Turn Off Calculations via Code for Slow Macros. See Macro Code via Manual Calculation. Assume Your Macro Will Fail, so Turn Back on In any Error Trapping.
13. Use VbNullString Over ""
14. Turn off Sheet/Workbook Events if They are Not Needed While Macro is Running. That is, Application.EnableEvents = False and Don't Forget to Turn Back on and Assume Your Macro Will Fail, so Turn Back on In any Error Trapping.
15. Make Good use of With Statements When Working With Objects.
16. Select Case is Often Better Than Mutiple If Else Statements.
17. IIf is Slower Than If Else.
18. Use Boolean Logic Over If Statements. E.g bYesNo = Range("MyValue") = 5.
19. Use Named Ranges Over Cell Addresses.
20. Use Meaningful Variable Names and Precede Them With Their Data Type. E.g lngRowCount NOT Meaningless Names Like x, y, z etc.
21. Capitalize at Least 1 Character in Variable Names and When Using in a Procedure use ALL Lower Case. Excel Will Convert as To the Case Used When Dimensioned.

An Explanation of Trappable Errors in Visual Basic for Apps