Passing Data to Procedures

You write a procedure to perform a series of steps. The exact object on which the procedure performs its task can vary. For example, you might 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.

Boring technical stuff on the Decimal data type

When perusing the VBA Help and drop-down menus, you might come across the Decimal data type. We omitted the Decimal data type from Table 4-1 because it just flat-out doesn't work. If we had included the Decimal data type in the table, its acceptable range would be +/-79,228,162,514,264,337,593,543,950,335 with no decimal point; +/-7.9228162514264337593543950335 with 28 places to the right of the decimal; smallest non-zero number +/-0.0000000000000000000000000001. The Decimal data type's storage size would be 14 bytes, and it would have no type declaration character.

The obscure Decimal data type does exist, but you can't declare an item as being of that data type. Instead, you have to declare the item as a Variant data type with a subtype of Decimal. For example, Dim X as Variant defines a variable X as a variant; X = CDec( value) stores value in X as a Decimal data type.

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 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 showMsg() 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) End Sub

These lines define a Sub procedure named showMsg() that accepts one argument: a string (text) named msgText. We just made up the msgText name — we could have used any name we 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 Sub() first line in Figure 4-2 defines a procedure named showMsg() that accepts three arguments: msgText, bttns, and msgTitle (all names we made up off the top of our heads). As you can see, msgText and msgTitle are both declared as the String data type, and bttns is declared as the Integer data type.

Figure 4-2:

A Sub procedure can accept different arguments.

Figure 4-2:

A Sub procedure can accept different arguments.

Sub procedure named showMsg()...

...accepts three arguments named

MsgText, bttns, and msgTitle.

Sub procedure named showMsg()...

...accepts three arguments named

MsgText, bttns, 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 accept incoming data and return a value. Thus, a Function procedure is like any function that's built in to Access. For example, the built-in Date() function always returns the current date.

^^TtW To see for yourself that Date() always returns the current date, type ? [(Oil Date() in the Immediate window and press Enter. You 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 shown here:

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 lines are only the first and last lines. The programmer would have to add more code between them for the procedure to do anything.)

Figure 4-3:

Functions accept arguments and return values.

Figure 4-3:

Functions accept arguments and return values.

...and returns a True/False value. ... accepts an argument frmName...

The Function procedure named isOpen()...

From the standpoint of modifying existing code, the argument list inside the parentheses tells you what data is passed to the procedure and as which data type. Code within the procedure can then work on the data that was passed, by simply 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.

0 0

Post a comment