Learn Access 2003 VBA with The Smart Method

90

www.LearnAccessVBA.com

Lesson 5-1: Understand data

types

Up until now we haven’t informed VBA about the data type of the

variables we’re working with. No professional programmer would

dream of using variables in this way. By telling VBA that a variable has a

data type we can prevent a whole class of bugs that are caused by data

type errors. For example, if we try to calculate the sum of two string

variables this will only trigger an error if VBA knows that their data type

is string.

Before discussing how we strongly type in code let’s review the data

types that are available and discuss where they might be used.

Session5

Session Five: Professional Grade VBA

© 2007 The Smart Method Ltd

91

String

String variables can contain up to 64,000 characters so are great for

storing variables such as names and addresses.

Currency

The currency data type is a precise data type. This means that, unlike

floating point types, numbers are stored at exactly their value. Always

use this data type for money values rather than Doubles.

Boolean

The simple True/False variable. Internally Booleans are implemented as

integers that can only have a value of either 0 (for False) or -1 (for True).

Date

Date variables are stored internally as numeric variables with the Date

part as the integer and the Time part following the decimal place. The

integer part stores the number of days elapsed since December 30

th

1899

(negative if before) and the decimal part as the elapsed time during that

day since midnight.

For example: 6pm on January 1

st

1900 is stored as 1.75 (also see sidebar).

Double

A Double is a floating point number that can be used to store both huge

and tiny numbers. For example, the double is able to store a number well

in excess of the total number of atoms in the universe.

The problem with the Double type is that not all numbers in a Double’s

range can be expressed in binary form so many numbers must be stored

as approximate values. This approximation often causes rounding errors

that are unacceptable in financial systems. Never use this data type for

monetary values.

Integer/Long Integer

Integers are a throw-back to earlier versions of VBA. In this version of

VBA they are converted (by the compiler) to Long Integers removing any

memory saving advantage and making them slower to execute because

of the conversion overhead. Always use the Long Integer data type to

precisely store whole numbers.

Variant

You created a Variant data type in the last session when the type was not

declared. With very few exceptions, Variants should never be used.

tip

Now that you know the secrets

of Microsoft’s internal

representation of the Date/Time

data type you are empowered

to do some interesting things

with date mathematics. This is

equally applicable to Excel

spreadsheets as the date

implementation is the same.

Try putting two dates into an

Excel spreadsheet and then

subtract one from the other.

Format the result as numeric

and you’ll see the elapsed days

between the dates (the integer

part) and the difference

between the two times (the

decimal part).

For example, the following

dates were subtracted from

each other:-

01/01/1997 05:00

31/03/1997 17:00

The result was 89.50.

89 = The number of elapsed

days between 1

st

January 1997

and 31

st

March 1997.

0.5 = The fraction of a day (24

hours) between 5am and 5pm.

12/24 = 0.5.

NB: If you want to work this

example through in Excel you’ll

have to format the cells with the

custom format:

dd/mm/yyyy hh:mm