Hi guys,
I thought I would start this "optimisation tips" thread, to gather tips from the best. I have already learnt through previous posts a few "rules of thumb" and other "best programming practices", but I wanted to go further here, by gathering whatever you consider as the best practices in VBA.
So, here is what I already know...
1) Variable declaration rules:
- Add a prefix related to the variable's type (str for strings, o for Ranges or Objects, etc.)
- Use names that explain what variables are about (some autoexplanatory name is the best)
- Replace all Integers by Longs. The reason was explained to me by fumei in a previous post:
- Destroy Set object instances before exiting a routine (Set object = Nothing). Again, fumei gave more detail about it:Integers are no longer used at all by VBA. You can declare them, but the parser converts them to Long on its initial parse pass.
In theory, when you exit a routine with a Set object (for example Set oHF = a headerfooter object), that object is destroyed. But there is still persistent issues that pop up with application objects - i.e. "extra" instances of Word.
2) Mind the scope of your variables. Nothing is more stupid to think a variable is global when it is local. That leads to many mistakes. Avoid using similar names for global and local variables. You only look for trouble.
Now, I am not sure (if someone can tell) if there is any advantage in declaring a local variable and use it as an argument of a function rather than declaring it as global.
3) Use Range rather than Selection
Though this is another world of programming, Range is definitely more efficient than Selection, at all levels: number of required lines, speed, versatility
4) Whenever possible, use an Iif statement instead of an If Else End loop. Unless I am mistaken, this is perfect for one line If loops, such as:
A more efficient way would be:If A>B then A=B else A=C end if
A= Iif(A>B,B,C)
5) Now, I wonder if writing an If statement on one line is more efficient than writing it on 3 lines?
orIf A=B then strText = "blabla" End if
I like better the 3-line code, as it looks nicer (and more obvious on the screen), but if the 1-line programming is more efficient, I would go for it...If A = B then strText = "blabla"
6) Any other tip or suggestion?
Please feel free to comment or add your tips, whatever they are!