Variables and Data Types

A variable can be thought of as a memory location that can hold values of a specific type. The value in a variable may change during the life of the program—hence the name “variable.”

In VBA, each variable has a specific data type, which indicates which type of data it may hold. For instance, a variable that holds text strings has the data type String and is called a string variable. A variable that holds integers (whole numbers) has the data type Integer and is called an integer variable. For reference, Table 5-1 shows the complete set of VBA data types, along with the amount of memory that they consume and their range of values. (I will discuss a few of the more commonly used data types in a moment.)

Table 5-1. VBA Data Types


Size in Memory

Range of Values


1 byte

0 to 255


2 bytes

True or False


2 bytes

–32,768 to 32,767

Long (long integer)

4 bytes

–2,147,483,648 to 2,147,483,647

Single (single-precision real)

4 bytes

Approximately –3.4E38 to 3.4E38

Double (double-precision real)

8 bytes

Approximately –1.8E308 to 4.9E324

Currency (scaled integer)

8 bytes

Approximately –922,337,203,685,477.5808 to 922,337,203,685,477.5807


8 bytes

1/1/100 to 12/31/9999


4 bytes

Any Object reference


Variable length: 10 bytes + string length; fixed length: string length

Variable length: <= about 2 billion (65,400 for Win 3.1); fixed length: up to 65,400


16 bytes for numbers 22 bytes + string ...

Get Writing Word Macros, Second Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.