Access VBA Macro Programming

Data Types

A variable can be given a data type that determines the type of data it can store. This can have

an effect on the efficiency of your code. If there is no data type, the default type is Variant.

Variant

Avariantcan store all kinds of data, whether it is text, numbers, dates, or other information.

It can even store an entire array. A variant variable can freely change its type at runtime,

whereas one that has been specified as, say, a string cannot. You can use the function

VarTypeto find out the type of data held by a variant:

Sub TestVariables()

stemp = "richard"

MsgBox VarType(stemp)

stemp = 4

MsgBox VarType(stemp)

End Sub

The message box will first display 8, which means it is a string. It will then display 2, which

means it is an integer.

Table 2-1 shows the return values for specific data types.

VBA always uses the most efficient means of storing data in a variant. As you can see

from the preceding example, it automatically changes to suit the data stored in it.

If you perform a mathematical operation on a variant that is not a numeric value, you will

get a Type MisMatch error. This means you are trying to put a data type into a variable not set

up to hold that data type—a bit like banging a square peg into a round hole. In this case, it may

be that you are trying to perform a mathematical operation on a variant holding a string of text.

18 Microsoft Access 2010 VBA Macro Programming

ReturnValue Type

0 Variant

1 Null

2 Integer

3 Long

4 Single

5 Double

6 Currency

7 Date/Time

8 String

11 Boolean

17 Byte

Table 2-1 VarType Return Values