Learn Access 2003 VBA with The Smart Method

92

www.LearnAccessVBA.com

Lesson 5-2: Understand and

implement strong typing and

explicit variable declaration

Weakly typed variables (that will always be of data type Variant) will

often result in problems such as “penny rounding" errors in financial

calculations. They also introduce the possibility of many potential bugs

remaining undetected in your code. For this reason they are never, ever,

used by professional programmers.

In the few cases where variants are useful a professional programmer

would still strongly type the variable as type Variant.

We’re going to strongly type the variables created earlier to tell VBA that

their data type is string.

We’re also going to improve our code yet further by switching on VBA’s

explicit variable declaration option in order to force all future variables to be

declared.

1

Open the VBACode.mdb database (if not already open) and

open frmTest in Design View.

2

Open the VBA Editor.

3

Edit the code in the ThankYouMessage sub.

In order to demonstrate the benefits of explicit variable declaration

we’ll introduce a bug. Change the line:

Call MsgBox(strMessage)

To:

Call MsgBox(strMassage)

Note the misspelling of strMassage in the second case.

4

Execute the buggy code.

Return to Form view and click the cmdPressMe command button.

An empty message box is displayed.

This happens because VBA created a new variable called

strMassage when it was encountered for the first time. Because

this new variable contained no text an empty message box was

displayed.

Consider the problems this could cause if you misspelt

dblProfitMargin as dblProfitMorgin and then used this value to

sell all of your goods at cost price!

It would be a better thing for this type of spelling mistake bug to

be trapped as soon as it occurs. We can do this with explicit

variable declaration.

5

Switch on explicit variable declaration.

Select Tools Options from the main menu and then click the

Editor tab. Check the Require Variable Declaration check box.

Session5

Session Five: Professional Grade VBA

© 2007 The Smart Method Ltd

93

This will add an Option Explicit statement at the top of every future

code module you create. As the module we’ve been working on

already exists we will need to add the statement manually.

6

Add an

Option Explicit

statement at the very top of the code

window.

7

Declare and strongly type the

strMessage

variable in the

ThankYouMessage

sub procedure by editing your code to

match the following:

Option Explicit

Private Sub cmdPressMe_Click()

Call ThankYouMessage

End Sub

Sub ThankYouMessage()

Dim strMessage As String

strMessage = "Thank you for pressing me"

Beep

Call MsgBox(strMassage)

End Sub

Note that the spelling mistake of strMassage is still present.

8

Execute the code

Return to Form view and click the cmdPressMe command button.

The view is switched to the code editor window with the error

highlighted:

Click the OK button and correct the error by typing strMessage over

the misspelt variable.

The code remains frozen with Sub ThankYouMessage()

highlighted in yellow.

9

Resume code execution by clicking the Continue button

on the VBA Editor toolbar.

The de-bugged code shows the Message Box correctly:

note

You may have noticed that

there is also an Option Compare

Database declaration

automatically set by Access.

This dictates the default

comparison method to use

when string data is compared

and can be set to Database,

Binary or Text.

The Option Compare Database

declaration results in string

comparisons based upon the

sort order determined by the

locale ID of the database where

the string comparisons occur.

Most Access applications will

use this setting as it is the most

logical choice.

important

It is possible to declare more

than one variable within a

single Dim statement but this

practice is frowned upon by

professional programmers.

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.

For this reason we have a

coding standard that demands

that variables be declared

individually.

This rule is also included in

Appendix A-The Rules.