Browsing the ‘net this morning I found the “Golden Rules” listed below at Ozgrid.com
The rules are described as things that “should not be optional”, which seemed to me in some cases to be going way too far, since for many of them their use or not really depends on what you are doing. I’ve given my comments below. If anyone else wants to chip in, please do 🙂
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.
This one I agree; I can’t think of any occasion when Option Explicit is not worth doing. I would add always declare the variable type when you dimension a variable (Option Explicit does not require this, but you should do it)
2. Split Procedures Into Logical 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.
I agree with both of these as well. I would add that in many cases it is worth writing functions so that they can be used as User Defined Functions as well, so they can be called either from the spreadsheet or from another VBA routine. This is not only useful in practice, it can be an excellent aid to checking and de-bugging.
4. Try & Keep all Related Procedures in the Same Module. Excel Compiles Each Module as a Procedure in the Module in Run.
OK, but watch out for the 64 kB module limit.
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.
It depends what you are doing. Consider if there is a better way of doing it, but for many purposes a loop is the obvious and right way to do what needs doing. “Avoiding” loops strikes me as strange advice.
8. Loops Through Object Collections are the Fastest, Compared to Other Loop Types.
I’m skeptical about this one, but if speed is critical then check and see what’s quickest.
9. Don’t Assume Code is Needed. Often a Worksheet Function is FAR better and More Efficient.
I’d say: don’t assume a worksheet function is best, often VBA is more efficient, faster, more easily maintainable, more easily reusable, and overall FAR better.
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.
No! Sure there are cases where worksheet functions may be quicker, but that’s far from always the case. I’d say avoid deeply nested worksheet functions and always consider whether the application could be done more efficiently with a UDF.
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.
OK – but also avoid writing data back to the spreadsheet as far as possible (except for the end result!), which is the prime cause of triggering recalculations which can dramatically slow down macro performance.
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.
OK, but efficiently written code will not be unduly slowed down by having enabled events, so I’d suggest only switch off if you need to.
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.
OK, but if you like Iif the difference would rarely be significant.
18. Use Boolean Logic Over If Statements. E.g bYesNo = Range(“MyValue”) = 5.
Not sure about the reasoning behind this one, my suggestion would be to use what you find most readable, which for me would be an If statement.
19. Use Named Ranges Over Cell Addresses.
OK in general, the exception being for debugging or one-of coding, where writing to a range specified by cell addresses is quicker and won’t appear in the final (or re-used) code.
20. Use Meaningful Variable Names and Precede Them With Their Data Type. E.g lngRowCount NOT Meaningless Names Like x, y, z etc.
Certainly use (short) meaningful names in general, but I have no problem with single character names for counters for instance. Just do what you find most efficient. As for preceding names with their data type (or an even more elaborate code system), this for me just makes the code significantly less readable, for very little gain.
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.
If this means entering code in lower case, so you can instantly see a typo (because the variable name does not become capitalised when you press enter), then I agree. A very useful technique with no downside that I can see.