Access VBA Macro Programming

TheCallcommand calls your subroutine and passes any required parameters to it. It then

executes the code in the subroutine and returns to the next instruction following theCall

statement. In this particular case, it passes the string “my subroutine” into the variable called

target.

If the subroutine you have written does not use parameters (arguments), you can run it

from the code page by selecting Run | Run Sub/UserForm from the VBE (Visual Basic

Editor) menu, pressingF5, or clicking the Run symbol on the toolbar. The cursor must click

on the subroutine you intend to run. This is a useful way of testing the code you have written

and seeing if it has any bugs.

Subroutines are a useful way of breaking large projects down into manageable pieces so

you do not end up with enormous and cumbersome routines. It is far easier to break a

problem into constituent parts and work separately on each section, making sure you get that

section working properly before moving onto others. The alternative is to write a large chunk

of code, which inevitably leads to unnecessary duplication.

Writing a Simple Function

The object of this exercise is to create a function to accept two numbers, which then multiplies

them together and returns the result. The function will have the nameMultiply. The following

table cites the four main mathematical operators you will use when writing functions and

subroutines in VBA.

Add +

Subtract –

Multiply *

Divide /

The code for this function is as follows:

Function Multiply(a, b)

Multiply = a * b

End Function

It should look like Figure 3-1. As with the subroutine, you must have at a bare minimum

the function line and the end function line (header and footer). Note that it must be entered in

a module you have inserted, not a module belonging to a form or report.

The header introduces two parameters,aandb, by showing them in parentheses after the

title of the function. A comma separates the two arguments. These arguments represent the

two numbers to be multiplied—they could be called anything, as long as the variable name is

consistent throughout the function.

30 Microsoft Access 2010 VBA Macro Programming