There are a number of data types that you can use in VBA. The details of these are set out in Table 2-2.
Name | Description | Type-Declaration Character | Range |
---|---|---|---|
Integer | 2-byte integer | % | ‚ 32,768 to 32,767 |
Long | 4-byte integer | & | ‚ 2,147,483,648 to 2,147,438,647 |
Single | 4-byte floating point number | ! | ‚ 3.402823E38 to 1.401298E-45 (negative values) |
Double | 8-byte floating point number | # | ‚ 1.79769313486232D308 to |
Currency | 8-byte number with fixed decimal point | @ | ‚ 922337203685477.5808 to 922337203685477.5807 |
Fixed Length String | String of characters ‚ fixed length | $ | 0 to approximately 65,500 characters |
Variable Length String | String of characters ‚ variable length | $ | 0 to approximately 2 billion characters |
Variant | Date/Time, floating point number or string | None | Date Values: January 1, 0000 to December 31, 9999; numeric values: same range as double; string values: same range as string |
If you only work with whole numbers , then you declare your variables as Integer or Long, dependent on size . Mathematical operations are much faster and memory demands are less for these types.
If you are working with fractions of numbers, then you 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 temp as Integer
Dim temp as Long
Dim temp as Currency
Dim temp as Single
Dim temp as Double
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. For a more detailed description, see the section ‚“Functions ‚½ in Chapter 5.
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 .