VBA The Golden Rules


The following Rules are from Ozgrid's site. These rules are good guiding principles as your VBA coding improves. They have helped me out on numerous occasions and are very close to the mark when it comes to optimising VBA code. A link to Dave Hawley's seminal VBA golden rules can be found at the bottom of the page.

  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 Separate Separate 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 Efficient than Custom Function Written in VBA.
  11. Avoid Macros That Delete Rows/Column/Cells. Make Use of AutoFilter, AdvancedFilter or Sort. 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.
  12. Use VbNullString Over ""
  13. 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.
  14. Make Good use of With Statements When Working With Objects.
  15. Select Case is Often Better Than Mutiple If Else Statements.
  16. IIf is Slower Than If Else.
  17. Use Boolean Logic Over If Statements. E.g bYesNo=Range("MyValue")=5.
  18. Use Named Ranges Over Cell Addresses.
  19. Use Meaningful Variable Names and Precede Them With Their Data Type. E.g lngRowCount NOT Meaningless Names Like x, y, z etc.

Link to The Golden Rules at Ozgrid Forum by the great Dave Hawley (RIP Dave).

The Golden Rules by Dave Hawley