MS-Access / Getting Started

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
NameDescriptionType-Declaration CharacterRange
Integertwo-byte integer%-32,768 to 32,767
Longfour-byte integer&-2,147,483,648 to 2,147,438,647
Singlefour-byte floating point number!-3.402823E38 to 1.401298E-45 (negative values)
1.401298E-45 to 3.402823E38 (positive values)
Doubleeight-byte floating point number#-1.79769313486232E308 to -4.94065645841247E-324 (negative values) 4.94065645841247E-324 to 1.79769313486232E308 (positive values)
Currencyeight-byte number with fixed decimal point@@-922337203685477.5808 to 922337203685477.5807
Fixed Length StringString of characters- fixed length$0 to approximately 65,400 characters
Variable Length StringString of characters- variable length$0 to approximately 2 billion characters
VariantDate/Time, floating point number, or stringDate 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.

[Previous] [Contents] [Next]