Defining data types

VBA makes life easy for programmers because it can automatically handle all the details involved in dealing with data. Not all programming languages make it so easy. For example, some languages are strictly typed, which means that the programmer must explicitly define the data type for every variable used.

Data type refers to how data is stored in memory—as integers, real numbers, strings, and so on. Although VBA can take care of data typing automatically, it does so at a cost: slower execution and less efficient use of memory. (There's no such thing as a free lunch.) As a result, letting VBA handle data typing may present problems when you're running large or complex applications. If you need to conserve every last byte of memory, you need to be on familiar terms with data types. Another advantage to explicitly declaring your variables as a particular data type is that VBA can perform some additional error checking at the compile stage. These errors might otherwise be difficult to locate.

Table 8-1 lists VBA's assortment of built-in data types. (Note that you can also define custom data types, which I describe later in this chapter in "User-Defined Data Types.")


Data Type Bytes Used Range of Values

Byte 1 byte 0 to 2 55

Boolean 2 bytes True or False

Integer 2 bytes -32,768 to 32,767

Long 4 bytes -2,147,483,648 to 2,147,483,647

Single 4 bytes -3.402823E38 to -1.401298E-45 (for negative values);

1.401298E-45 to 3.402823E38 (for positive values)

Double 8 bytes -1.79769313486232E308 to

-4.94065645841247E-324 (negative values); 4.94065645841247E-324 to 1.79769313486232E308 (for positive values)

Currency 8 bytes -922,337,203,685,477.5808 to


Decimal 14 bytes +/-79,228,162,514,264,337,593,543,950,335

with no decimal point;

+/-7.9228162514264337593543950335 with 28 places to the right of the decimal


Table 8-1 VBA BUILT-IN DATA TYPES (Continued)

Data Type

Bytes Used

Range of Values


8 bytes

January 1, 0100 to December 31, 9999


4 bytes

Any object reference


10 bytes +

0 to approximately 2 billion

(variable length)

string length



1 to approximately 65,400

(fixed length)

of string


16 bytes

Any numeric value up to the range of a double data type

(with numbers)


22 bytes +

0 to approximately 2 billion

(with characters)

string length



Varies by element

The Decimal data type was introduced in Excel 2000,and it cannot be used in previous versions. This is a rather unusual data type because you cannot actually declare it. In fact,it is a subtype of a variant.You need to use the VBA CDec function to convert a variant to the decimal data type.

Generally, it's best to use the data type that uses the smallest number of bytes yet still can handle all the data assigned to it. When VBA works with data, execution speed is a function of the number of bytes that VBA has at its disposal. In other words, the fewer bytes used by data, the faster VBA can access and manipulate the data.

Benchmarking Variant Data Types

To test whether data typing

is important, I developed the following routine, which

performs some meaningless calculations in a loop and then displays the procedure's

total execution time:

Sub TimeTest()

Dim x As Integer

y As Integer

Dim A As Integer,

B As Integer, C As Integer

Dim i As Integer,

j As Integer

Dim StartTime As

Date, EndTime As Date

' Store the starting time

StartTime = Timer ' Perform some calculations x = 0

For j = 1 To 5000 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 5.2 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 14.7 seconds, nearly 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.

For worksheet calculation, Excel uses the Double data type, so that's a good choice for processing numbers in VBA when you don't want to lose any precision. For integer calculations, you can use the Integer type if you're sure that the values will not exceed 32,767. Otherwise, use the Long data type. When dealing with Excel worksheet row numbers, you'll want to use the Long data type because the number of rows in a worksheet exceeds the maximum value for the Integer data type.

Was this article helpful?

0 0


Post a comment