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