Access VBA Macro Programming

Return to the initial Hello World example in Chapter 1. Turn the “Hello World” statement

into a comment by putting a single quote (') character before it and enter the following code

so it looks like this:

Sub MyCode()

'MsgBox "Hello World"

x = Multiply(3, 5)

MsgBox x

End Sub

Note that when you type the wordMultiplyand open the brackets, VBA automatically

displays the parameters it is expecting by name. By inserting the function into the code, you

are forcing a call to that function using the parameters 3 and 5 to replaceaandbin your

function. The result is returned in the variablex.

Click the cursor anywhere on the MyCode procedure and run the code by clicking the Run

icon in the VBE toolbar or pressingF5. You will see a message box displaying 15.

Public and Private Functions and Subroutines

VBA allows you to define your functions or subroutines as public or private using the

keywordPublicorPrivate. For example:

Private Sub PrivateSub()

End Sub

Any subroutines or functions you create are public by default. This means they can be

used throughout the modules within your application, and database users will find the

subroutines available to use in any VBA code that may write in the database. They will also

be able to access public functions in your code and either use them in their own code or their

own SQL queries.

The one exception to this is forms/reports. As will be discussed in Chapter 9, forms

represent dialog forms and have their own modules. Reports also have modules. A public

subroutine or function on a form can be called from other modules within your code by

referencing the form object—for example,Form_Form1.MysubRoutine. If you do not

reference the subroutine to the form name as shown earlier, or the subroutine is made private,

you will get a compile error when this is run.

Using private declarations, you can have procedures that have the same names but are in

different modules. That procedure is private to that module and cannot be seen by other

modules. More importantly, it cannot be seen and run by the Access user in SQL queries.

This can cause confusion both for the programmer and for VBA. Which one does VBA

choose to invoke if you call that procedure? Fortunately, VBA has a set of rules it uses for

this. It first looks in the current module where the code is executing. If it cannot find a

procedure of that name there, it scans all modules for the procedure. Calls within the module

where the private procedure is defined will go to that procedure. Calls outside that module

will go to the public procedure.

32 Microsoft Access 2010 VBA Macro Programming