You write a procedure to perform a series of steps. The exact object on which the procedure performs its task can vary. For example, suppose you inherit a database table of names and addresses, with everything typed in uppercase letters, as in JOHN SMITH. You want to convert all that text to proper case (John Smith), but you don't want to retype it all.
You could write a procedure to do the conversion for you, but you wouldn't want the procedure to fix just one name or one address. You want the procedure to be flexible enough to fix all the names and addresses in the table with the click of a button. In other words, you want Access to hand over some piece of information, like JOHN SMITH, and then have the procedure return back John Smith. However, you want it to do that with any text you pass to it, including JANE DOE and P.O. BOX 123 and HANK R. MCDOUGAL.
If you want a procedure to accept information from the outside world (so to speak), you have to tell the procedure what type of data to expect and where to put the data. You do so within the parentheses that follow a procedure's name within a module. What you type is the argument list (or arglist, for short). The syntax for each argument is name As type where name is any name of your choosing, and type is one of the data type names listed in Table 4-1. For example, if you want to create a Sub procedure named showMsgBox() and pass one parameter named msgText to it as text, the first line of the procedure needs to contain msgText As String as an argument, as in the following example:
Sub showMsg(msgText As String)
The above lines define a Sub procedure named showMsg() that accepts one argument: a string (text) named msgText. The msgText name I just made up myself: I could have used any name I wanted. The As String tells the rest of the procedure to expect text to be passed.
You can pass multiple bits of information to a procedure as long as each has a unique name, and you give each a data type. Separate each name and type with a comma. For example, the mySub() first line in Figure 4-2 defines a procedure named showMsg() that accepts three arguments: msgText, bttns, and msgTitle (all names I made up off the top of my head). As you can see, msgText and msgTitle are both declared as the String data type, and bttns is declared as the Integer data type.
Sulii showMsg (msgText As String, bttns As Integer, msgTitle As String)
A Sub procedure can accept Sub procedure named showMsg()..
...accepts three arguments named different arguments.
MsgText, btns, and msgTitle.
Although a Sub procedure can accept incoming data through its arguments, it can't return any data to Access or other VBA procedures. A Function procedure, on the other hand, can both accept incoming data and return a value. Thus, a Function procedure is like any function that's built into Access. For example, the built-in Date() function always returns the current date.
To see for yourself that Date() always returns the current date, type ? Date() into the Immediate window and press Enter. You'll see today's date.
When you want your own custom procedure to return a value, you have to define the data type of the value being returned. The name of the return value is always the same as the function name, so you don't include a name. And because you're defining a return value, you place the declaration outside the closing parenthesis, as follows:
Function name(arglist) As type End Function where name is the name of the function, arglist defines any incoming arguments (exactly as it does in a Sub procedure), and type is the data type of the value that the function returns. The type placeholder must match one of the data type names listed in Table 4-1.
Figure 4-3 shows an example where the first line defines a Function procedure named isOpen() that accepts a string as an argument and then returns a True or False value. (Note: Those are only the first and last lines. The programmer would have to add more code between them for the procedure to actually do anything.)
Function isOpen (f rmNaitte As String) As Boolean
End Function accept
...and returns a True/False value. ... accepts an argument frmName...
arguments and return values.
TheFunction procedure named i sOpen()...
From the standpoint of modifying existing code, the argument list inside the parentheses tells you what data is passed to the procedure and as what data type. Code within the procedure can then work on the data that was passed, simply by referring to it by name. Within a procedure, you use variables to store and manipulate little chunks of data, like the values passed to a procedure. Variables are a big part of all programming languages, so spend some time getting to know them.
Was this article helpful?