Excel VBA Data Types

Variables are characterized by the data types they represent. VBA variables may be declared as being one of the data types listed below.

Caution: Whenever a variable is set to a value outside its storage range, an overflow error message will be generated and program execution stops.

Numeric Data Types

Byte: - a variable that can only store eight bits 2^8 = 256 unique values. In VBA, these values are the integers between
0 and 255
In other languages this range may differ, but it will always be limited to 256 unique values.
Currency: - a non-integer numeric variable that can take on values between
-922,337,203,685,477.5808 and 922,337,203,685,477.5807
This type of variable stores only to four decimal places and is used in financial applications.
Integer: - a whole (non-fraction) number that can take on positive or negative values and the number 0. VBA integer variables can only take on values between
-32,768 and 32,767
When working with medium to large datasets, or applications performing a large number of calculations, it is best to avoid declaring variables as Integer.
Long: - a variable that takes on integer values between
-2,147,483,648 and 2,147,483,647
LongLong: - a variable that can take on integer values between
-9,223,372,036,854,775,808 and 9,223,372,036,854,775,807
LongPtr: - a variable that is Long on 32-bit machines and LongLong on 64-bit machines
Single: - a single precision variable stores a floating point number that can take on negative values between
-3.402823 x E38 and -1.401298 x E-45
and positive values between
1.401298 x E-45 and 3.402823 x E38
Double: - a double precision variable stores a floating point number that can take on negative values between
-1.79769313486232 x E308 and -4.94065645841247 x E-324
and positive values between
4.94065645841247 x E-324 and 1.79769313486232 x E308
Decimal: - stores absolute (non-floating point) numbers that can take on values between
+/-79,228,162,514,264,337,593,543,950,335with no decimal point
+/-7.9228162514264337593543950335 with 28 places to the right of the decimal
The smallest non-zero numbers are +/-0.0000000000000000000000000001.

Non-numeric Data Types

Boolean: - variables that only take on the logical values of TRUE or FALSE, each of which uses up to 2 bytes of space
Date: - a variable that stores dates between January 1, 100, to December 31, 9999; certain VBA functions allow attaching time to date
Object: - not really a variable, but a reference to an object that is treated like a variable by VBA
String: - a variable that stores data as characters (text) and can accommodate strings of length 0 to about 2 billion
String * N: - a string variable limited to a fixed number (N = integer) of characters
User-defined: - a data type, usually a ragged array, defined by the user
Variant: - a variable that can be set to any other data type and the type can change from step to step within a routine

If you do not specify a data type, the Variant data type is assigned by default.