Excel 2010 Bible

Chapter 44: VBA Examples

901

Besides simplifying your coding, using object variables also speeds your macros quite a bit. I’ve

seen macros execute twice as fast after creating object variables.

Declaring variable types

Usually, you don’t have to worry about the type of data that’s assigned to a variable. Excel handles

all these details behind the scenes. For example, if you have a variable named MyVar, you can

assign a number of any type to it. You can even assign a text string to it later in the procedure.

If you want your procedures to execute as fast as possible, though, you should tell Excel in

advance what type of data is going be assigned to each of your variables. Providing this informa-

tion in your VBA procedure is known as declaring a variable’s type.

Table 44.1 lists all the data types that are supported by VBA. This table also lists the number of

bytes that each type uses and the approximate range of possible values.

TABLE 44.1

VBA Data Types

Data Type Bytes Used Approximate Range of Values

Byte 1 0 to 255

Boolean^2 True or False

Integer 2 –32,768 to 32,767

Long (long integer) 4 –2,147,483,648 to 2,147,483,647

Single (single-precision

floating-point)

4 –3.4E38 to –1.4E–45 for negative values; 1.4E–45 to

4E38 for positive values

Double (double-precision

floating-point)

8 –1.7E308 to –4.9E–324 for negative values; 4.9E–324

to .7E308 for positive values

Currency (scaled integer) 8 –9.2E14 to 9.2E14

Decimal 14 +/–7.9E28 with no decimal point

Date 8 January 1, 100 to December 31, 9999

Object 4 Any object reference

String (variable-length) 10 + string length 0 to approximately 2 billion

String (fixed-length) Length of string 1 to approximately 65,400

Variant (with numbers)^16 Any numeric value up to the range of a Double

Variant (with characters) 22 + string length Same range as for variable-length String

User-defined (using Type) Number required by

elements

Range of each element is the same as the range of its

data type