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.
OK
6. Use a Sheet CodeName Over The Tab Name or Index Number.
OK
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.
OK
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 “”
OK
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.
OK
16. Select Case is Often Better Than Mutiple If Else Statements.
OK
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.
Newton Excel Bach, not (just) an Excel Blog 
My number one rule for code speed is: Don’t worry about. Write clear, maintainable code then test its speed and fix the bottlenecks. You can’t predict the bottlenecks anyway, so don’t try.
LikeLiked by 1 person
1,4-6,12-16,19: yes, for the most part
2,3,7-11,17,18: it depends
2,20,21: agree in part
Much depends on what your VBA is actually intended to do. As for optimization, I follow the advice of Michael Jackson (not that one): “The First Rule of Program Optimization: Don’t do it. The Second Rule of Program Optimization (for experts only!): Don’t do it yet.”
LikeLike
Many of the rules are poorly phrased variants that mean to say “DON’T REINVENT THE WHEEL!” There are many things Excel already does that too many VBA coders replace with VBA code. Iterating through a 1D range to locate a value is a classic example. Either the Find method of the Range class or Application.WorksheetFunctions.Match would make more sense.
As for UDFs, a few general UDFs is better than many overly specialized UDFs. It’s also a matter of taste and background. For some people a single UDF may be clearer than several nested built-in functions, but there CAN BE significant performance penalties calling UDFs too frequently.
Re 19: these are GENERAL rules. They’re NOT APPLICABLE to one-off code. For production code, use of hardcoded range addresses *IS* pure evil.
Re 21: if you use short variable names, there’s minimal opportunity for typos. An alternative is always compiling code when you think it’s correct.
LikeLike
Thanks for the feedback guys.
So would we agree that the points in that list are useful things to think about, but the “best practice” given should not be seen as things that “are not optional”?
(Other than No 1 that is)
LikeLike
“8. Loops Through Object Collections”
Dumb wording. I doubt looping through object collections is faster than looping through an array of long integers.
I’ve heard that
For Each Something In SomethingCollection
is faster than
For i = 1 To SomethingCollection.Count
With SomethingCollection(i)
LikeLike
Also…
“16. Select Case”
I’ve heard that Select Case is slower than If ElseIf Then Else.
But some of these rulse will not be noticeable in most situations. Use what is easier to read, and for me that’s Select Case.
LikeLike
Dont Dim as Interger Dim as long.
LikeLike
I’ve just recently done a few performance benchmarks, and posted results to my blog: http://roymacleanvba.wordpress.com/2010/01/13/performance-range-access/
and following posts.
One conclusion is that the overhead of getting/setting Range.Value probably dwarfs every other performance consideration – other than not doing myString = myString + extraString a zillion times inside a loop.
I don’t really buy the squiddly statement-level tips, like one type of loop being faster than another, or Select Case being slower than If Else.
I don’t bother with datatype prefixes on local variables – just clutter – but do put a module/class (not type) prefix on module/class variables.
Range names, absolutely.
Should probably use Long rather than Integer all the time.
LikeLike
Yes, I agreee that minimising the number of times that data is transferred between the spreadsheet and VBA is by far the most significant way to improve performance in most cases.
But more important “good practice” (in my opinion) is the things that will prevent your work ending up with the wrong answer (especially if it looks like it could be a right answer). Perhaps the biggest danger there is in passing data to a sub-routine byref when it should have been byval, or vice versa.
I think I might do a separate post on that.
LikeLike
Late comment re: the speed of iterating collections with For Each vs. by index…
Accessing a single element of a collection by index takes a time proportional to the index number, just like accessing an element of a linked list in some other language would. This means that looping through a collection by index number takes a time proportional to the *square* of the length of the collection.
For Each will iterate a whole collection in a time proportional to the length of the collection. Presumably there is some internal iterator that VBA gets to use when it sees For Each.
LikeLike
Hi John – late comments are welcome 🙂
I’m not a big user of collections (in fact come to think of it I’m not even a small user of collections), but I found what you said a bit surprising so I did some trials and found you are absolutely right. I still find it surprising, but I guess there must be some trade off between flexibility and speed.
I also did a check using arrays and found that the time taken for iterating by index was proportional to the length of the array, but using For Each was almost 3 times faster (for any size array). I wasn’t aware of that (and I don’t understand why the big difference), but it’s certainly worth knowing.
I’ll write up the results of my tests in more detail in a separate post some time.
LikeLike
I’ll be looking forward to that, and I enjoy your blog in general.
I think the flexibility/speed trade off for a collection is that of the traditional “list” data structure. For all I know, it is implemented internally with a linked list. The methods to add elements before/after other elements suggest that might be the case. In theory the benefit you get in return for the cost of O(n) random access time is O(1) insertion time, but I’ve never tested that with VBA collections. (It’s frustrating that there is so little technical information out there and we’re sitting here in 2010 guessing and doing our own empirical tests…why can’t the help just say “a collection has the performance characteristics of a list”?)
I’ve also done some array speed tests, and one caveat about For Each with arrays is that it (seemingly – who knows?) makes a copy of each array element into the loop variable. So if your array contains variants that have “big” values (say your array contains array variants), For Each can be quite a bit slower than iterating by index.
LikeLike
Pingback: The speed of loops « Newton Excel Bach, not (just) an Excel Blog
Very late followup, but this answer appeared on stackoverflow not long after we had this discussion. It appears the Collection object is implemented with a linked list internally:
http://stackoverflow.com/questions/2548526/what-are-collections-implemented-as-in-vb6/2549502#2549502
LikeLike
Thanks John. Presumably that means if you use a For…Next loop it searches from the start of the collection at each loop, which would certainly explain the performance difference.
For anyone interested in more details of the difference in performance, I posted details at: https://newtonexcelbach.wordpress.com/2010/03/07/the-speed-of-loops/
LikeLike