VBA supports various data types, and choosing the right one has a direct impact on execution speed, memory usage, and preventing type conversion errors.
In this article, we’ll review all numeric and non-numeric data types in VBA along with their value ranges, memory sizes, and recommended variable prefixes.
🔢 Numeric Data Types in VBA
VBA provides several numeric data types that differ in precision, memory usage, and value range.
The following table summarizes this information:
| Data Type | Value Range | Memory Usage | Prefix |
| Byte | 0 to 255 | 1 byte | byt |
| Integer | -32,768 to 32,767 | 2 bytes | int |
| Long | -2,147,483,648 to 2,147,483,647 | 4 bytes | lng |
| LongLong1 | -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 | 8 bytes | – |
| LongPtr2 | -2,147,483,648 to 2,147,483,647 (32-bit) -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 (64-bit) | 4 bytes – 32-bit 8 bytes – 64-bit | – |
| Single | -3.402823E+38 to -1.401298E-45 (negative) 1.401298E-45 to 3.402823E+38 (positive) | 4 bytes | sng |
| Double | -1.79769313486232E+308 to -4.94065645841247E-324 (negative) 4.94065645841247E-324 to 1.79769313486232E+308 (positive) | 8 bytes | dbl |
| Currency | -922,337,203,685,477.5808 to 922,337,203,685,477.5807 | 8 bytes | cur |
| Decimal3 | +/-79,228,162,514,264,337,593,543,950,335 (no decimals) +/-7.9228162514264337593543950335 (28 decimals) | 14 bytes | – |
| Variant (with numbers) | Includes all Double values | 16 bytes | vnt |
2. Not an actual data type; it maps to Long on 32-bit systems and LongLong on 64-bit systems. Useful for cross-platform compatibility.
💡 Tip:
For financial calculations requiring high precision, use the Currency type. It prevents rounding errors common with floating-point arithmetic.
📘 Example:
Dim curSalary As Currency
curSalary = 125000.75Non-Numeric Data Types in VBA
Non-numeric data types are used to store text, dates, objects, and data structures.
The following table lists their main characteristics:
| Data Type | Value Range | Memory Usage | Prefix |
|---|---|---|---|
| Boolean1 | True or False | 2 bytes | bln |
| Collection | Unknown | Unknown | col |
| Date | January 1, 100 to December 31, 9999 | 8 bytes | dtm |
| Dictionary | Unknown | Unknown | – |
| Object | Any object reference | 4 bytes | obj |
| String (variable-length) | 0 to approximately 2 billion characters | 10 bytes + string length | str |
| String (fixed-length)2 | 1 to about 65,400 characters | String length | str |
| Variant (with characters) | 0 to approximately 2 billion characters | 22 bytes + string length (24 bytes on 64-bit) | vnt |
| User-defined (using Type)3 | Depends on declared field types | Depends on declared field types | – |
2. To declare a fixed-length String, specify the length after an asterisk (*). Example:
Dim s As String * 33. Defined using the Type statement.
💡 Tip:
In VBA, String variables can be either variable-length or fixed-length.
When working with large text files or memory-sensitive operations, using a fixed-length string can improve performance.
📘 Example:
Dim strCode As String * 3
strCode = "ABC"⚙️ Tips for Choosing the Right Data Type
🔸 Speed vs. Memory:
Types such as Integer and Long use less memory, but if you need decimal precision, use Single or Double.
🔸 Limit the use of Variant:
Although Variant is flexible and can hold any data type, it slows execution and increases memory usage.
🔸 Consider Office version and system architecture:
If your code will run on both 32-bit and 64-bit systems, use LongPtr to ensure compatibility without changing the data type manually.
📚 Reference
Data Type Summary – Microsoft Docs
🧭 Summary
Choosing the proper data type in VBA is one of the foundations of professional code design.
When working on large projects or heavy Excel files, always consider the expected value range and select the type accordingly.
In the next post, we’ll cover Type Conversion and functions such as CInt, CDbl, CStr, and more.