Learn Access 2003 VBA with The Smart Method

72

www.LearnAccessVBA.com

Lesson 4-1: Understand subs

Most real-world tasks can be broken down into smaller tasks. In

programming we call tasks procedures. Procedures can be split into Sub

procedures. Consider the procedure of opening your front door. It can be

broken down into several sub-procedures:

Insert key

Turn key anti-clockwise

Push door inwards

Remove key

Close door

Defining procedures in VBA code

In VBA we cannot leave spaces in procedure names so we would name

the above sub-procedures:

InsertKey, TurnKeyAntiClockwise, PushDoorInwards, RemoveKey and

CloseDoor.

In VBA code we use the keywords Sub and End Sub to de-lineate

procedures and sub-procedures. There’s no distinct difference between a

Sub-procedure and a Procedure as they are all de-lineated by the Sub…

End Sub statements.

Most VBA programmers refer to sub-procedures simply as Subs. The

InsertKey sub would thus be typed into the editor as follows:

Sub InsertKey()

‘ InsertKey VBA code will go here

End Sub

Note two more things about the above code. We have added parenthesis

after the Sub Procedure’s name and also a single quotation mark in front

of the comment InsertKey VBA code will go here. Comments in code are

ignored by VBA and can be used to add comments to describe what your

code does and how it works.

Calling procedures in VBA code

To execute all of the sub procedures we have discussed and open the

door we would call the subs in sequence like this:

Sub OpenDoor()

Call InsertKey

Call TurnKeyAntiClockwise

Call PushDoorInwards

Call RemoveKey

Call CloseDoor

End Sub

tip

Always use the Upper/Lower

Case naming convention for

Sub names (as used in

TurnKeyAntiClockwise)

Never use underscores in your

own sub names and then it will

always be clear which subs are

event handlers.

Never, ever, abbreviate words,

for example do not use sub

names such as

TrnKeyAntiClkwse.

These rules (and the reasons for

them) are listed in Appendix A:

The Rules.

note

Sometimes you’ll hear the term

Sub Routine used instead of

Sub Procedure. You’ll also hear

some people refer to Sub

Procedures as simply

Procedures.

None of this terminology is

incorrect; we just have many

different words that refer to

exactly the same thing.

We will simply use the word

Sub in this book.

Session4

Session Four: An Introduction To VBA

© 2007 The Smart Method Ltd

73

1

Open the VBACode.mdb database created in the last

chapter (if not already open).

2

Open the frmTest form’s event handler for the cmdPressMe

button’s Click event.

Open frmTest in Design View. Right click on the Command Button

and select Build Event from the shortcut menu. The code for the

Command Button’s Click event is displayed in the code editor.

3

Add a new Sub procedure called ThankYouMessage.

Do this by adding the following code:

Sub ThankYouMessage()

End Sub

4

Remove the Beep statement from the cmdPressMe_Click

Sub procedure and add the following code to the

ThankYouMessage() Sub procedure:

Sub ThankYouMessage()

Beep

Call MsgBox("Thank you for pressing me")

End Sub

In its present form the code will not do a thing when the command

button is pressed. We need to call the ThankYouMessage sub

procedure from the cmdPressMe_Click() event handler.

5

Add a call to the ThankYouMessage sub procedure to the

cmdPressMe_Click event handler.

Private Sub cmdPressMe_Click()

Call ThankYouMessage

End Sub

6

Test the command button.

Display the form in Form View. When you click the command

button you should now hear a beep and see the message.

Full code listing

Private Sub cmdPressMe_Click()

Call ThankYouMessage

End Sub

Sub ThankYouMessage()

Beep

Call MsgBox("Thank you for pressing me")

End Sub

tip

You do not have to use the Call

keyword when calling a sub or

function but most professional

programmers would always

use this coding style as it makes

code far more readable by

allowing arguments (if any) to

be contained by parenthesis.

For example:-

Call MsgBox(“Hello")

Instead of:-

MsgBox “Hello"

If you are programming in a

team environment it is useful to

agree common coding

standards such as this so that

each member of the team is

able to immediately understand

other team member’s code.

This rule (and others) is listed

in Appendix A: The Rules.

note

The MsgBox() call is actually to

a special type of sub called a

function.

Functions can be regarded as

being the same as a sub except

that they are able to return a

value. The MsgBox sometimes

returns a value to indicate

which message box button was

pressed (some MsgBox’s have a

Yes and No button).

We’ll learn all about functions

in a later lesson.