Microsoft Office Tutorials and References
In Depth Information
Variables, Data Types, and Constants
use the Integer type (which is limited to values less than or equal to 32,767). Otherwise, use
the Long data type. In fact, using the Long data type even for values less than 32,767 is
recommended, because this data type may be a bit faster than using the Integer type. When dealing
with Excel worksheet row numbers, you want to use the Long data type because the number of
rows in a worksheet exceeds the maximum value for the Integer data type.
Benchmarking variant data types
To test whether data typing is important, I developed the following routine, which performs
more than 300 million meaningless calculations in a loop and then displays the procedure’s total
execution time:
Sub TimeTest()
Dim x As Long, y As Long
Dim A As Double, B As Double, C As Double
Dim i As Long, j As Long
Dim StartTime As Date, EndTime As Date
‘ Store the starting time
StartTime = Timer
‘ Perform some calculations
x = 0
y = 0
For i = 1 To 10000
x = x + 1
y = x + 1
For j = 1 To 10000
A = x + y + i
B = y - x - i
C = x / y * i
Next j
Next i
‘ Get ending time
EndTime = Timer
‘ Display total time in seconds
MsgBox Format(EndTime - StartTime, “0.0”)
End Sub
On my system, this routine took 7.7 seconds to run. (The time will vary, depending on your
system’s processor speed.) I then commented out the Dim statements, which declare the data
types. That is, I turned the Dim statements into comments by adding an apostrophe at the
beginning of the lines. As a result, VBA used the default data type, Variant . I ran the
procedure again. It took 25.4 seconds, more than three times as long as before.
The moral is simple: If you want your VBA applications to run as fast as possible, declare your
variables!
A workbook that contains this code is available on the companion CD-ROM in a file named
timing text.xlsm .
 
Search JabSto ::




Custom Search