Excel 2019 Bible

Chapter 47: Seeing Some VBA Examples

47

Data Type Bytes Used Approximate Range of Values

Double (double-preci-

sion 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

If you don’t declare a variable, Excel uses the Variant data type. In general, the best
approach is to use the data type that uses the smallest number of bytes yet one that can
still handle all of the data assigned to it. An exception is when you’re performing floating-
point calculations. In such a case, it’s always best to use the Double data type (rather
than the Single data type) to maintain maximum precision. Another exception involves
the Integer data type. Although the Long data type uses more bytes, it usually results in
faster performance.

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 that data uses, the faster that VBA can
access and manipulate the data.

To declare a variable, use the Dim statement before you use the variable for the first
time. For example, to declare the variable Units as a Long data type, use the following
statement:

Dim Units As Long

To declare the variable UserName as a string, use the following statement:

Dim UserName As String

If you declare a variable within a procedure, the declaration is valid only within that pro-
cedure. If you declare a variable outside of any procedures (but before the first procedure),
the variable is valid in all procedures in the module.