I guess the title is pretty straightforward, what impact does declaring variables as variants, instead of a specific datatype, has on performance in VBA?
Maybe it's because I am not that experienced with programming, but intuitively it should decrease performance due to the computer having to check which datatype is assigned, and then changing the variant to the assigned datatype, instead of immediately assigning the value to it. I have not been able to find some literature on this.
It depends. Variants are slower then native types, but in most programs, it doesn't matter at all. Most macros are small and get compiled when run and the diference may be a few microseconds, that you can't perceive.
Variants have their advantages, I love them.
So it depends on what you are doing. If your program runs in a blink of an eye, there is no advantage in avoiding variants. If it takes time strongly type your variables (and declare objects correctly - see below).
If performance is an issue there are other things apart from this that I'll mention below.
General Issues
When setting properties or calling methods, each is a function call in the CPU. That means stack setup overhead. Function calls are slower than inline code. Use loops rather than functions in VBA forthe same reason.
F or a start don't specify all those properties over and over again. Unless you change them they don't change.
With Selection.Find
.ClearFormatting
.Replacement.ClearFormatting
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchByte = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = False
.MatchFuzzy = False
For loop to go through each word pair
.Text = SrcText
.Replacement.Text = DestText
.Find.Execute Replace:=wdReplaceAll
Next
End With
Minimise Dots
So if you are interested in performance minimise dots (each dot is a lookup), especially in loops.
There are two ways. One is to set objects to the lowest object if you are going to access more than once.
eg (slower)
set xlapp = CreateObject("Excel.Application")
msgbox xlapp.worksheets(0).name
(faster because you omitt a dot every time you use the object)
set xlapp = CreateObject("Excel.Application")
set wsheet = xlapp.worksheets(0)
msgbox wsheet.name
The second way is with. You can only have one with active at a time.
This skips 100 lookups.
with wsheet
For x = 1 to 100
msgbox .name
Next
end with
String Concatination
And don't join strings one character at a time. See this from a VBScript programmer. It requires 50,000 bytes and many allocation and deallocation to make a 100 character string.
http://blogs.msdn.com/b/ericlippert/archive/2003/10/20/53248.aspx
Reading Properties
Don't reread properties that don't change especially if out of process or late bound. Put them into a variable.
Object Types
Two concepts here - in or out of process and early or late binding.
exefiles are connected to out of process. All calls are marshalled over RPC (a networking protocol). Dllfiles are in process and function calls are made direct with a jump.
Early binding is set x = objecttype. Functions are looked up when you write the program. On execution the program is hard coded to jump to address stored in the vtable for that function.
Late binding is set x = createobject("objecttype"). Each function call goes like this. "Hi object do you have a print command". "Yes", it replies, "command number 3". "Hi object can you please do command number 3". "Sure, here's the result".
From Visual Basic Concepts (part of Help)
You can make your Visual Basic applications run faster by optimizing the way Visual Basic resolves object references. The speed with which Visual Basic handles object references can be affected by:
Whether or not the ActiveX component has been implemented as an in-process server or an out-of-process server.
Whether an object reference is early-bound or late-bound. In general, if a component has been implemented as part of an executable file (.exe file), it is an out-of-process server and runs in its own process. If it has been implemented as a dynamic-link library, it is an in-process server and runs in the same process as the client application.
Applications that use in-process servers usually run faster than those that use out-of-process servers because the application doesn't have to cross process boundaries to use an object's properties, methods, and events. For more information about in-process and out-of-process servers, see "In-Process and Out-of-Process Servers."
Object references are early-bound if they use object variables declared as variables of a specific class. Object references are late-bound if they use object variables declared as variables of the generic Object class. Object references that use early-bound variables usually run faster than those that use late-bound variables.
Excel Specific
See this link from a Microsoft person. This is excel specific rather than VBA. Autocalc and other calc options/screenupdating etc.
http://blogs.office.com/2009/03/12/excel-vba-performance-coding-best-practices/
Is using variants in vba bad for performance? - Yes!
Variant should be avoided wherever possible. (There are of cource some legitimate uses)
