Learn Access 2003 VBA with The Smart Method

286

www.LearnAccessVBA.com

7: Constants

7-1

Name constants using upper case and underscores.

Programmers should always be aware of which variables are constants. Clearly identify them by

always using the upper-case/underscore style for constant names (and only for constant names). It

will then always be easy to differentiate between variables and constants in your code.

Good:

TSM_APPLICATION_STATUS

Bad:

conApplicationStatus

You’ll often see code written using the “bad" convention above. Note that Microsoft do not observe

this rule with their own constants such as vbRed.

7-2

Constants should always be named with a prefix that is unlikely to be used by any other third

party code.

Microsoft use the prefix vb for all of their constants. You should use at least three letters, perhaps

your own initials or the name of the application you are writing. As there are 27

3

combinations of

three-letter prefixes it is unlikely (though not impossible) that your constant prefixes will coincide

with those used by a third party library.

8: Variable declaration

8-1

The Require Variable Declaration option must always be switched on.

This can be done by selecting Tools Options from the code editor window. The Option Explicit

command will then be included at the top of every new (but not existing) module.

8-2

All variables must be strongly typed.

This includes variables declared as parameters for subs and functions.

Good:

Dim strFirstName as string

Sub DeleteCustomer( lngCustomerID as Long)

Bad:

Dim strFirstName

Sub DeleteCustomer( lngCustomerID)

8-3

All variables must be declared and typed with a dedicated Dim statement.

It is possible to declare more than one variable within a single Dim statement but this practice can

cause problems.

At first glance you may think that:

Dim strOne,strTwo as string

Was functionally equivalent to:

Dim strOne as String

Dim strTwo as String

The first example would, in fact, declare strOne as a Variant and only strTwo as a string.

Good:

Dim strFirstName as string

Dim strLastName as string

Bad:

Dim strFirstName, strLastName as string

'

first variable is a variant

Appendix A: The Rules

© 2007 The Smart Method Ltd

287

8-4

When declaring variables and constants include an in-line comment detailing their purpose.

Example:

dim lngFormMode as long ' Can be TSM_FORM_INSERT or TSM_FORM_UPDATE

dim lngCompanyCount as long ' Used to iterate through rsCompanies

dim blnCompanyIsActive as boolean ' Flags current credit status

9: Error handling

9-1

Error handling must be implemented in every sub and function without exception.

Programmers often argue that some code is so simple that it can never fail so does not need error

handling. While this may be true in some cases there’s nothing wrong with a catch-all approach. If

absolutely every subroutine has error handling you cannot possibly confront your user with an

unprofessional and confidence-sapping runtime error. When you take this approach it is comforting

to find that those bullet-proof subs that couldn’t possible fail often do, but when they do the error is

gracefully handled.

9-2

Whenever SetWarnings methods are used in a function or sub a call to SetWarnings(True) must

be included immediately before the single exit point (within the clean up code section).

If code branches to the Error Handler after the DoCmd.SetWarnings(False) method call but before

you re-enable standard Access warnings with a DoCmd.SetWarnings(True) method call there will be

no more standard warnings during the entire Access session. This could be potentially disasterous.

Adding a precautionary SetWarnings(True) call within the cleanup section eliminates exposure to

this problem.

10: Object destruction

10-1

Recordsets that are opened must be explicitly closed.

10-2

All objects that are instantiated must be explicitly destroyed when no longer needed.

This has been a “hot topic" amongst VBA programmers and fiercely debated on the programming

bulletin boards for years.

Some programmers argue that Access can automatically de-reference object variables when they go

out of scope (in the same way that other types of variables do).

It is widely believed that memory leaks (a situation where a computer gradually grinds to a halt as

the memory becomes exhausted and then needs to be re-booted) are often caused by relying upon

automatic object destruction.

It is instructive to examine the wizard-generated code for Access switchboards. The code within the

HandleButtonClick function shows that Microsoft also find value in closing recordsets and explicitly

destroying objects when working with their own product.