1. Wherever you see several lines lines of very similar code look for the common bits and consider how you can use a variable (e.g. a loop counter) and cut out all the common lines.
2. Use small procedures for one specific task only and link them from a main 'Control' procedure. Don't keep adding lines of code to a procedure just because you can.
3. Don't Loop when you can filter.
4. Use Error Handling so your code exits gracefully when needed.
5. Always indent your code properly.
6. Apply and use a naming convention consistently (e.g variable declarations).
7. Use sheet CodeName when ever possible, instead of Name or Index.
8. Use Early Binding (some exceptions to this rule).
9. Explicitly use ByRef or ByVal if a procedure takes arguments.
10. Avoid Option Compare Text.
11. Use class modules for global variables.
12. If you have a non-general error trap (ie a condition where an error is expected) put that specific code in a submodule or function.
13. Use descriptive variable names (with possible exception of transient local variables).
14. Keep scoping as local as possible with prefixes to identify the scope as well as variable type.
15. Declare all variables appropriate to usage.
16. Use Variants only when necessary ((i) To receive an argument that may be of varying type (string, Boolean, number, or arrays of same); (ii) To receive a value from a cell than may be of varying type; (iii) To receive the value of a multi-cell range from a worksheet; (iv) To receive an indefinite number of arguments (ParamArray is a variant); (v) To receive the results from many worksheet functions, like MMult and Array; (vi) To receive the value of a property that may return Null; (vii) To receive the keys of a dictionary; (viii) To be the control variable of a For Each loop when not a more closely-aligned data type is available (e.g., a Range); (ix) To be the return type of a function that returns data of varying type, or (on Mac) an array of variable size).
17. Don’t select cells, sheets, workbooks, or other objects except as necessary.
18. Generally, use built-in (worksheet) functions when possible (there are some exceptions).
19. Don’t use loops if there’s a way around it; if there isn’t, prefer iterating elements from a collection (For Each/Next), followed by For/Next and Do/Loop.
20. Select Case statements are prettier, If/ElseIf/Else/EndIf statements are faster. Either should be arranged to test cases from most likely to least.
21. Use collections and dictionaries in preference to other data structures when possible, and use dictionaries instead of collections, unless ordering is required.
22. Use the Like operator sparingly. Ditto for Regular Expressions. Avoid text comparison entirely when there are other options (e.g., TypeOf versus TypeName).
23. The Len function is among Excel's fastest; all it has to do is grab the first word of a string, where the length is stored. So rather than If s <> "", use If Len(s) (and there’s no need to coerce a number to a Boolean so it can be converted back to a number).
24. Write logical, clear code first, and then optimize for speed. Don’t obscure the structure and intent of code for speed unless you don’t mind having the person that has to maintain it later casting aspersions on your lineage.
25. Comment your code minimally beyond the procedure level; it’s impossible to keep code and comments in sync. Instead, make code shine in clarity and simplicity.
26. Set Calculation to manual during execution. If worksheet calculation is necessary in the course of macro execution, calculate only the minimum range necessary.
27. Turn off ScreenUpdating during execution.
28. Turn off events if necessary to avoid triggering change event macros and getting other listeners excited.
29. And use dictionaries instead of collections, unless ordering is required.
30. Don't use IIf.
31. Don't compare Boolean variables explicitly to True/False - it serves no purpose.
Bookmarks