Access VBA Macro Programming

NOTE

Null is not 0. This is an easy but incorrect assumption to make when you’re starting to learn VBA.

The safest way to check for a Null value in your code is to useIsNull. Other methods,

such as the statementIs Null, may not give correct results.

Sub TestNull()

temp=Null

Msgbox IsNull(temp)

End Sub

The result here will be True (nonzero).

Other Data Types

Why use data types other than Variant? Because Variant may not use the best data type for the

purpose. If you want to create concise fast code, you need other data types. For example, if

you are doing lots of mathematical calculations on relatively small integer numbers, you can

gain an enormous speed advantage by using the data type Integer instead of Variant. You can

use Variant by default, but Variant will not necessarily assume it is an integer being used. It

could assume it is a floating point number, in which case calculations will take far longer,

although the same result will be produced.

Memory considerations should also be taken into account. Each double number takes up

8 bytes of memory, which may not seem like a lot. However, across a large array it can use a

large amount of RAM, which would slow the process down. This will use up memory in the

computer, which Windows could be using as virtual memory for its graphical display.

VBA Data Types

A number of data types can be used in VBA. The details of these are set out in Table 2-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

20 Microsoft Access 2010 VBA Macro Programming