VBA Data Types

The computer cannot differentiate between the numbers (1, 2, 3,…) and strings (a, b, c,…). To make this differentiation, we use the Data Types.

The data type you assign to a variable will be dependent on the type of data you want that variable to hold.

In VBA, there are many data types. We divide the data types into two main categories, namely numeric and non-numeric data types.

Below is the table that shows all the available data types you can use in VBA Excel.

Numeric Data Types

TypeStorageRange of Values
Byte1 byte0 to 255
Integer2 bytes-32,768 to 32,767
Long4 bytes-2,147,483,648 to 2,147,483,648
Single4 bytes-3.402823E+38 to -1.401298E-45 for negative values 1.401298E-45 to 3.402823E+38 for positive values.
Double8 bytes-1.79769313486232e+308 to -4.94065645841247E-324 for negative values 4.94065645841247E-324 to 1.79769313486232e+308 for positive values.
Currency8 bytes-922,337,203,685,477.5808 to 922,337,203,685,477.5807
Decimal12 bytes+/- 79,228,162,514,264,337,593,543,950,335 if no decimal is use +/- 7.9228162514264337593543950335 (28 decimal places)

Non-Numeric Data Types

Data TypeBytes UsedRange of values
String (fixed-length)Length of string1 to 65,400 characters
String (variable length)Length + 10 bytes0 to 2 billion characters
Boolean2 bytesTrue or False
Date8 bytesJanuary 1, 100 to December 31, 9999
Object4 bytesAny embedded object
Variant (numeric)16 bytesAny value as large as double
Variant (text)Length + 22 bytesSame as variable-length string
User-definedVariesThe range of each element is the same as the range of its data type.

Note: if the data type is not specified, it will atomically declare the variable as a variant in VBA.

When you specify the data type of a variable in code, it tells VBA to how to store the variable, and how much space has to allocate for it.

For example, if you need to use a variable is meant to hold the month number, you can use the Byte data type (which accommodate values from 0 to 255). Since the month number is not going to be above 12, this will work fine and also reserve less memory for this variable.

And, if you need a variable to store the row numbers in Excel, you need to use a data type that can accommodate a number up to 1048756. So it’s good to use the Long data type

Next TopicVBA Variables