Learn Access 2003 VBA with The Smart Method

96

www.LearnAccessVBA.com

Lesson 5-4: Understand

functions

A function is a special type of sub that returns a value. Functions usually

(but not always) accept one or more arguments.

In this lesson we’ll create a new command button that adds two numbers

together and displays the result in a message box using your

ThankYouMessage sub procedure.

1

If it is not already open, open the VBACode.mdb

spreadsheet and open frmTest in Design View.

2

Add a new command button. Set the

Caption

property to

&Add Two Numbers

and the

Name

property to

cmdAddTwoNumbers

.

3

Open the command button’s Click event handler.

Right click on the Add Two Numbers Command Button and select

Build Event from the shortcut menu and then Code Builder from the

Choose Builder dialog. The code for the Command Button’s Click

event is displayed in the code editor.

4

Add the following code to the cmdAddTwoNumbers_click

event handler and create your first function:

AddTwoNumbers()

:

Private Sub cmdAddTwoNumbers_Click()

Dim dblFirstNumber As Double

Dim dblSecondNumber As Double

Dim dblSumOfNumbers As Double

dblFirstNumber = InputBox("Enter the first number: ")

dblSecondNumber = InputBox("Enter the second number: ")

dblSumOfNumbers = AddTwoNumbers(dblFirstNumber, _

dblSecondNumber)

Call ThankYouMessage("The sum of the numbers is: " & _

dblSumOfNumbers, _

"Result")

End Sub

Function AddTwoNumbers(dblFirstNumber As Double, _

dblSecondNumber As Double)

AddTwoNumbers = dblFirstNumber + dblSecondNumber

End Function

note

You may wonder why VBA did

not report an error when you

concatenated a string

expression with a double in the

line:

“The sum of the" & _

“numbers is: “ _ &

dblSumOfNumbers

It would have been better

coding practice to explicitly

convert the datatype with the

following code:

“The sum of the" & _

“numbers is: “ _ &

CStr(dblSumOfNumbers)

The ability of VBA to perform

automatic type conversion (in

this case a double to a string)

can be useful to beginners but

is a thorn in the side of

professional programmers who

would rather it never

happened.

The new versions of stand-

alone VB (VB-Net or VB2005)

have addressed this problem by

allowing programmers to

suppress the automatic type

conversion behaviour.

Unfortunately it isn’t possible

to suppress this “feature" in

VBA.

Session5b

Session Five: Professional Grade VBA

© 2007 The Smart Method Ltd

97

We have already encountered the MsgBox() VBA function. Now

we’re encountering the InputBox() function for the first time.

The InputBox function puts up a dialog box on the screen to

request input from a user and then returns the value that they type

in.

You should have enough understanding from the previous lesson

to be able to figure out how most of the rest of the code works.

The only line that may not be intuitive is:

AddTwoNumbers = dblFirstNumber + dblSecondNumber

In order to tell a function which value it should return, the name of

the function (in this case AddTwoNumbers) is stated followed by

the assignment operator (=) and the value that you wish to return.

5

Test the command button.

Switch to Form view. When you click the command button you

should see the first input box displayed. Enter a numeric value and

then click the OK button.

The second input box is displayed. Enter another numeric value

and then click the OK button.

The result is then displayed in a message box (using the

ThankYouMessage sub procedure).

note

Even though this routine will

work most of the time it is not

“bullet-proof".

If a user enters text instead of a

number the routine will crash

and display a rather user-

unfriendly error message.

Similarly if the user presses the

Cancel button on either of the

Input Boxes the program will

fail.

Later in this session we’ll learn

how to make the code more

robust by incorporating elegant

error handling.