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

Type Storage Range of Values
Byte 1 byte 0 to 255
Integer 2 bytes -32,768 to 32,767
Long 4 bytes -2,147,483,648 to 2,147,483,648
Single 4 bytes -3.402823E+38 to -1.401298E-45 for negative values 1.401298E-45 to 3.402823E+38 for positive values.
Double 8 bytes -1.79769313486232e+308 to -4.94065645841247E-324 for negative values 4.94065645841247E-324 to 1.79769313486232e+308 for positive values.
Currency 8 bytes -922,337,203,685,477.5808 to 922,337,203,685,477.5807
Decimal 12 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 Type Bytes Used Range of values
String (fixed-length) Length of string 1 to 65,400 characters
String (variable length) Length + 10 bytes 0 to 2 billion characters
Boolean 2 bytes True or False
Date 8 bytes January 1, 100 to December 31, 9999
Object 4 bytes Any embedded object
Variant (numeric) 16 bytes Any value as large as double
Variant (text) Length + 22 bytes Same as variable-length string
User-defined Varies The 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

Previous articlePowerShell Tutorial
Next articleVBA Date Format