A data type, such Integer or Boolean, is a classification of data. It determines the space the data occupies in memory, the values the data can store, and the operations can be done on the data. The following table lists the supported data types in Excel VBA with their storage sizes and value ranges.
| Data type | Storage size | Range of values |
| Byte | 1 byte | 0 to 255 |
| Boolean | 2 bytes | True or False |
| Integer | 2 bytes | -32,768 to 32,767 |
| Long | 4 bytes | -2,147,483,648 to 2,147,483,647 |
| Single | 4 bytes | -3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values |
| Double | 8 bytes | -1.79769313486231E308 to -4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values |
| Currency | 8 bytes | -922,337,203,685,477.5808 to 922,337,203,685,477.5807 |
| Decimal | 14 bytes | +/-79,228,162,514,264,337,593,543,950,335 with no decimal point; +/-7.9228162514264337593543950335 with 28 places to the right of the decimal |
| Date | 8 bytes | January 1, 100 to December 31, 9999 |
| Object | 4 bytes | Any object reference |
| String (variable-length) | 10 bytes + string length | 0 to approximately 2 billion characters |
| String (fixed-length) | Length of string | 1 to approximately 65,400 characters |
| Variant (with numbers) | 16 bytes | Any numeric value up to the range of a Double data type |
| Variant (with characters) | 22 bytes + string length | Same range as for variable-length String |
| User-defined (using Type) | Bytes required by elements | The range of each element is the same as the range of its data type. |
A Variant is a special data type. If a variable is declared without explicitly stating its data type, it is then, by default, of data type Variant. A variant variable can hold any kind of data, including special values such as Empty, Error, Nothing, and Null.
The data type of a variant variable changes when needed, depending on what manipulation is done on the variable. The following listing illustrates the point by checking the data type of a variant variable.
Sub CheckVariantDataType() Dim myvar As Variant myvar = Null Debug.Print TypeName(myvar) 'returns Null myvar = 3 Debug.Print TypeName(myvar) 'returns Integer myvar = myvar & 1 Debug.Print TypeName(myvar) 'returns String myvar = myvar * 2 Debug.Print TypeName(myvar) 'returns Double Debug.Print myvar 'returns 62 End Sub
The TypeName function is used to determine the data type of a variable.
VBA also allows you to define custom (also known as user-defined) data types. Often, custom data types are to group several related variables. The following definition of a custom data type named uStaffInfo illustrates the point:
Type uStaffInfo StaffName As String * 30 Addr(1 To 2) As String Age As Byte End Type
The definition of a custom data type must be placed at the top of a VBA module before any procedures.
Dissecting Excel VBA 