Access VBA Macro Programming

 Call your code from another VBA procedure. Code can form subroutines or functions

that can then be used within other macros written on the same spreadsheet. For example,

say you have to search a string of text for a particular character and you write a

subroutine to do this, using a parameter to pass the text string to the subroutine. You

can use this subroutine as a building block by calling it from anywhere else within

other procedures in exactly the same way you would a normal VBA keyword.

 Click directly on your code and pressF5.This is for development work only. For example,

if you are working on a subroutine in isolation, you may wish to run it only to see how

it works.

All these methods are dealt with in further detail later in the book.

A VBA project normally uses at least one module to store the necessary functions and

subroutines known as procedures. To insert a new module, simply select Insert | Module from

the VBE menu, and the new module will appear. Note that this contains only a general area

initially. It has no events, as with the form and report code sheets.

You can enter subroutines or functions here and make them public or private. The distinction

between public and private is to decide whether other modules within the same workbook can

access the procedure. If the code is private, it can only be used in the current module where it

resides. If it is public, it can be used by any other procedure in any other module in the database.

Should you have a subroutine you do not want used elsewhere in the code, make the subroutine

private. The default is always public.

The Difference Between Subroutines and Functions

Subroutines and functions are two types of code procedures. On casual inspection, they

appear to be the same. In truth, however, they are different.

Asubroutineis a piece of code that performs a set of actions or calculations, or a

combination of the two. It can form a “building block” within a program and may sometimes

need to be repeated. It can be called by several different routines. The programmer has to

write a subroutine only once, and it can be called from anywhere within the program as many

times as needed. However, it does not directly return a value; if it performs a calculation,

there is no direct way of finding the result. It can alter values of variables if you pass

parameters using the ByRef methodology, which is explained later in this chapter. It is called

by inserting aCallinstruction into the code, as shown here:

Sub Main()

Call MySub 'Calls another macro/procedure called MySub

End Sub

You do not have to use the wordCallto utilize the subroutineMySub. The following

example also works:

Sub Main()

MySub 'Calls another macro/procedure called MySub

End Sub

28 Microsoft Access 2010 VBA Macro Programming