VBA Data Types
A number of data types can be used in VBA. The details of these are set out in Table-2.
Numeric Types
If you only work with whole numbers, then you declare your variables as Integer or Long, depending on size. Mathematical operations are much faster and memory demands are less for these types.
If you are working with fractions of numbers, use Single, Double, or Currency. Currency (fixed decimal point) supports up to 4 digits to the right of the decimal point and 15 digits to the left. Floating point (Single and Double) have larger ranges but can produce small rounding errors.
Dim temp1 as Integer Dim temp2 as Long Dim temp3 as Currency Dim temp4 as Single Dim temp5 as Double
| Name | Description | Type-Declaration Character | Range |
|---|---|---|---|
| Integer | two-byte integer | % | -32,768 to 32,767 |
| Long | four-byte integer | & | -2,147,483,648 to 2,147,438,647 |
| Single | four-byte floating point number | ! | -3.402823E38 to 1.401298E-45 (negative values) 1.401298E-45 to 3.402823E38 (positive values) |
| Double | eight-byte floating point number | # | -1.79769313486232E308 to -4.94065645841247E-324 (negative values) 4.94065645841247E-324 to 1.79769313486232E308 (positive values) |
| Currency | eight-byte number with fixed decimal point | @@ | -922337203685477.5808 to 922337203685477.5807 |
| Fixed Length String | String of characters- fixed length | $ | 0 to approximately 65,400 characters |
| Variable Length String | String of characters- variable length | $ | 0 to approximately 2 billion characters |
| Variant | Date/Time, floating point number, or string | Date Values: January 1, 0000 to December 31, 9999; numeric values: same range as double; string values: same range as string |
String Types
If your variable will always contain text, you can declare it to be of type String:
Dim temp as String
You can then use string handling functions to manipulate it. You can take sections from it, search for a particular character, or turn it all into uppercase characters.
A string is of variable length by default. The string grows or shrinks according to the data in it. If you do not want this to happen, you can declare a fixed-length string by using String * size:
Dim temp as String * 50
This forces a string to be fixed at 50 characters in length. If your string is less than 50, it is padded with spaces. If it is greater than 50 characters, the excess characters are truncated and lost. So, although you do get control over the amount of memory being used because there is always a fixed length to each element, there is a risk of data loss if a user manages to input a longer string than you originally envisioned.