In this tutorial, you’ll learn about different numeric and non-numeric data types supported in VBA, as well as data type conversions. We encounter many types of data every day, such as name, date, bills, the price of a product, etc.
The data in each field belongs to a specific type, and their values cannot be separated from the type attached to them. VBA, too, has many types of data that are used to perform the desired action.
In VBA, there are many data types. We divide the data types into two main categories, namely numeric and non-numeric data types.
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 for no decimal points,+/-7.9228162514264337593543950335 for 28 places to the right of the decimal. |
The first data type that Visual Basic for Applications (VBA) supports is the basic Numerical type. These VBA data types include the following data shown in the table below
Non-Numeric Data Types
The second data type that Visual Basic for Applications (VBA) supports is the Non-Numerical data type. These VBA data types include the following data shown in the table below
| Data Type | Bytes Used | Range of Values |
| String (fixed length) | Length of string | 1 to approximately 65,400 |
| String (variable length) | 10 bytes added to the string length | 0 to 2 billion characters |
| Boolean | 2 bytes | True or False |
| Object | 4 bytes | Object in VBA |
| Date | 8 bytes | January 1, 100 to December 31, 9999 |
| Variant (numeric) | 16 bytes | Any value as large as double |
| Variant (text) | 22 bytes added to the string length | Same as variable length string |
| User-defined | Varies | The range of each element is the same as the range of its data type. |
The variant data type can be used if you are not sure what type of data your variable contains or if it is likely to change. It can be used with any kind of data, except the fixed-length String data type. The variant data type is declared in the following manner:
Dim my value as Variant
When you specify a variable’s data type in code, it tells VBA where to store it, and how much space it has to be allocated.
For example, if you need to use a variable that holds the month number, you can use the Byte data type (which accepts values from 0 to 255), as the month number will not be higher than 12, and this will take up less memory.
And, If you want to store row numbers in Excel as a variable, then use a data type that can store a number up to 1048756. So it’s good to use the Long data type.
