VBA Data Types

There are a number of data types that you can use in VBA. The details of these are set out in Table 2-2.

Table 2-2: Data Types Within VBA

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) 1.401298E-45 to 3.402823E38 (positive values)
Double	8-byte floating point number	#	‚ 1.79769313486232D308 to ‚ 4.94065645841247D-324 (negative values) 4.94065645841247D-324 to 1.79769313486232D308 (positive values)
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

Numeric Types

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 

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. 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 .