Passing Arguments to Procedures

A procedure's arguments provide it with data that it uses in its instructions. The data that's passed by an argument can be any of the following:

With regard to arguments, procedures are very similar to worksheet functions in the following respects:

♦ A procedure may not require any arguments.

♦ A procedure may require a fixed number of arguments.

♦ A procedure may accept an indefinite number of arguments.

♦ A procedure may require some arguments, leaving others optional.

♦ A procedure may have all optional arguments.

For example, a few of Excel's worksheet functions, such as RAND, use no arguments. Others, such as COUNTIF, require two arguments. Others still, such as SUM, can use an indefinite number of arguments (up to 30). Still other worksheet functions have optional arguments. The PMT function, for example, can have five arguments (three are required; two are optional).

Most of the procedures that you've seen so far in this book have been declared without any arguments. They were declared with just the Sub keyword, the procedure's name, and a set of empty parentheses. Empty parentheses indicate that the procedure does not accept arguments.

The following example shows two procedures. The Main procedure calls the ProcessFile procedure three times (the Call statement is in a For-Next loop). Before calling ProcessFile, however, a three-element array is created. Inside the loop, each element of the array becomes the argument for the procedure call. The ProcessFile procedure takes one argument (named TheFile). Notice that the argument goes inside parentheses in the Sub statement. When ProcessFile finishes, program control continues with the statement after the Call statement.

Sub Main()

Dim File(1 To 3) As String File(1) = "dept1.xls" File(2) = "dept2.xls" File(3) = "dept3.xls" For i = 1 To 3

Call ProcessFile(File(i)) Next i End Sub

Sub ProcessFile(TheFile)

Workbooks.Open FileName:=TheFile ' ...[more code here]... End Sub

You can also, of course, pass literals (that is, not variables) to a procedure. For example:

Sub Main()

Call ProcessFile("budget.xls") End Sub

You can pass an argument to a procedure in two ways: by reference and by value. Passing an argument by reference (the default method) simply passes the memory address of the variable. Passing an argument by value, on the other hand, passes a copy of the original variable. Consequently, changes to the argument within the procedure are not reflected in the original variable.

The following example demonstrates this concept. The argument for the Process procedure is passed by reference (the default method). After the Main procedure assigns a value of 10 to MyValue, it calls the Process procedure and passes

MyValue as the argument. The Process procedure multiplies the value of its argument (named YourValue) by 10. When Process ends and program control passes back to Main, the MsgBox function displays MyValue: 100.

Sub Main()

Dim MyValue As Integer MyValue = 10 Call Process(MyValue) MsgBox MyValue End Sub

Sub Process(YourValue)

YourValue = YourValue * 10 End Sub

If you don't want the called procedure to modify any variables passed as arguments, you can modify the called procedure's argument list so that arguments are passed to it by value rather than by reference. To do so, precede the argument with the ByVal keyword. This technique causes the called routine to work with a copy of the passed variable's data — not the data itself. In the following procedure, for example, the changes made to YourValue in the Process procedure do not affect the MyValue variable in Main. As a result, the MsgBox function displays 10 and not 100.

Sub Process(ByVal YourValue)

YourValue = YourValue * 10 End Sub

In most cases, you'll be content to use the default reference method of passing arguments. However, if your procedure needs to use data passed to it in an argument —and you absolutely must keep the original data intact — you'll want to pass the data by value.

A procedure's arguments can mix and match by value and by reference. Arguments preceded with ByVal are passed by value; all others are passed by reference.

If you pass a variable defined as a user-defined data type to a procedure, it must be passed by reference. Attempting to pass it by value generates an error.

Because I didn't declare a data type for any of the arguments in the preceding examples, all the arguments have been of the Variant data type. But a procedure that uses arguments can define the data types directly in the argument list. The following is a Sub statement for a procedure with two arguments of different data types. The first is declared as an integer, and the second is declared as a string.

Sub Process(Iterations As Integer, TheFile As String)

When you pass arguments to a procedure, it's important that the data that is passed as the argument matches the argument's data type. For example, if you call Process in the preceding example and pass a string variable for the first argument, you get an error: ByRef argument type mismatch.

Public Variables versus Passing Arguments to a Procedure

In Chapter 8, I point out how a variable declared as Public (at the top of the module) is available to all procedures in the module. In some cases, you might want to access a Public variable rather than pass the variable as an argument when calling another procedure.

For example, the procedure that follows passes the value of MonthVal to the ProcessMonth procedure:

Sub MySub()

Dim MonthVal as Integer ' ... [code goes here] MonthVal = 4

Call ProcessMonth(MonthVal) ' ... [code goes here]

End Sub

An alternative approach is

Public MonthVal as Integer

Sub MySub()

' ... [code goes here] MonthVal = 4 Call ProcessMonth ' ... [code goes here]

End Sub

In the revised code, because MonthVal is a public variable, the ProcessMonth procedure can access it, thus eliminating the need for an argument for the

ProcessMonth procedure.

Arguments are relevant to both Sub procedures and Function procedures. In fact, arguments are more often used in Function procedures. In Chapter 10, where I focus on Function procedures, I provide additional examples of using arguments with your routines, including how to handle optional arguments.

0 0

Post a comment