Software Development

Creating Access VBA subs and functions: Using arguments and public variables

To unleash the power of VBA in your Access applications, take this lesson in passing arguments to these subroutines or functions.

By Todd E. Parker

If you have started to discover the power of Microsoft Visual Basic for Applications (VBA) in your Access applications, I’d like to help you bump your game up a notch. Here’s an essential lesson in passing arguments to subroutines (subs) or functions.

The basics: What is an argument?
When you create a subroutine or function, you can require specific information be passed to it in order for it to do its job. An argument is a placeholder of information. For example, a postal worker needs to deliver mail to you. However, the postal worker is going to need your address in order to deliver the mail. We could say that your address is the argument, and when it is passed to the postal worker (the subroutine or function), the postal worker can now do his or her job.

A typical subroutine might look like this:
Public Sub CalcRoutine()

The same subroutine with an argument would look like this:
Public Sub CalcRoutine(MyInteger as Integer)

What is a public variable?
Typically, there are times in your application when you might want some information to be available at a later date. To explain, let’s use the postal example again. You might have called the post office to report your new address. However, the postal worker needs that information.

Now, the post office will declare that information as public. Even if the postal worker wasn’t present in the original exchange of information, through the use of the Public Variable, the postal worker can find your address anytime. Public variables are declared in the General Declarations of your code, which is the very beginning of the code when you enter into the Code Window. Note that Access automatically inserts certain code for you (“Option Compare Database”). You insert your public variables above this line of code.

So how can we implement this in an Access application? To demonstrate, I will create a form that requires an integer from the user, and will then perform a calculation using a subroutine that has as its argument an integer. Figure A shows a form that accepts the integer, a field that displays the result, and a command button that calls the CalcRoutine subroutine.

Figure A
Our sample form accepts an integer and performs a basic calculation.

Let’s take a look at the code behind our sample form. Below is the Public Variable you will declare in the General Declarations:
Public ResultNumber As Integer
'--- Access will automatically have this next line placed for you.
Option Compare Database

For the Calculate Command Button, you would insert code like this for the OnClick event:
Private Sub Calculate_Click()
'----- The following IF statements will make sure the user enters a valid number
If IsNumeric(Me.MyInteger) And MyInteger > 0 And MyInteger < 11 Then
'---- Now we will call our Sub Routine and pass an Argument
'---- The Code for CalcRoutine is in the following Sub Routine.
CalcRoutine (Me.MyInteger)
 MsgBox "Please enter on a number from 1 to 10 “ & _
“in the MyInteger field.", vbOKOnly, "Error"
End If

'--- After the CalcRoutine Function processes the calculation, place

'--- the result onto the form.
Me.ResultInteger = ResultNumber

End Sub

This is the CalcRoutine that requires an Argument:
Public Sub CalcRoutine(MyInteger As Integer)

Dim Multiplier As Integer

Multiplier = 2

'--- Now we will apply a calculated number to the Public Variable

'--- for use later in our Application
ResultNumber = MyInteger * Multiplier

End Sub

Now, you are probably asking yourself, “Why would I want to do all this when I can do all of this in the Click event of the Calculate Command Button?” It’s a valid question. You can replicate this entire procedure and reduce it into one small section of code in the Click event of the Calculate Command Button.

However, let’s now pretend that you do not know what your multiplier is. For example, your multiplier might be another variable that is located in a separate database. You could now include this data connection to identify the variable multiplier in the CalcRoutine. Another benefit would be that this Resulting Number is now available to all other forms in your Application, even if they are opened at a later time. Not only is it available to all of your other forms, it can even be used later, in another Sub Routine, Function, or Macro.

In summary, we have three different sections of code that are all working together to pass information along a specific route. There is the General Declarations section, which houses the Public Information, the Click event of the Calculate Command Button, which then passes the user-entered number onto the final section of code, the CalcRoutine. The above code could be considered incomplete because you should always include error handling routines in your code, but I’m going to assume you’ve already figured that much out.

Todd E. Parker is a developer for the Science Applications International Corporation in New Orleans.

To comment on this tip, please post a comment below or follow this link to write to Todd.