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
.