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. As a result, letting VBA handle data typing may present problems when you're running large or complex applications. Another advantage of 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.")

Table 8-1: VBA BUILT-IN DATA TYPES

+ Open table as spreadsheet

Table 8-1: VBA BUILT-IN DATA TYPES

+ Open table as spreadsheet

Data Type

Bytes Used

Range of Values

Byte

1 byte

0 to 2 5 5

Boolean

2 bytes

True or False

Integer

2 bytes

-32, 768 to 32, 767

Long

4 bytes

-2,147,483, 64 8 to 2,147,483, 647

Single

4 bytes

-3.40282 3E38 to -1.4012 9 8E-45 (for negative values); 1.401298E-45 to 3.402823E38 (for positive values)

Double

8 bytes

-1.79769313486232E308to-4.94 0 65 645 8 412 47E-32 4 (negative values); 4.94065645841247E-324 to 1.79769313486232E308 (for positive values)

Currency

8 bytes

-922,337,203,685,477.5808 to 922,337,203,685,477.5807

Decimal

12 bytes

+ /-7 9,228,162,514,2 64,337,593,543,950,335 with no decimal point;

+ /-7.92 28162 5142 64337593543950335 with 28 places to the right of the decimal

Date

8 bytes

January 1, 0100 to December 31, 9999

Object

4 bytes

Any object reference

String (variable length)

10 bytes + string length

0 to approximately 2 billion characters

String (fixed length)

Length of string

1 to approximately 65,400 characters

Variant (with numbers)

16 bytes

Any numeric value up to the range of a double data type. It can also hold special values such as Empty, Error, Nothing, and Null.

Data Type

Bytes Used

Range of Values

Variant (with characters)

22 bytes + string length

0 to approximately 2 billion

User-defined

Varies

Varies by element

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 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 5000

x =

x

+ 1

y =

x

+ 1

For

= 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.1 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, almost 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.

Note The Decimal data type is rather unusual 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 that will be 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.

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 (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.

0 0

Post a comment