Passing Arguments to Procedures | Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

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:

A variable
A constant
An array
An object

The use of arguments by procedures is 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 and NOW, use no arguments. Others, such as COUNTIF, require two arguments. Others still, such as SUM, can use up to 255 arguments. 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 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     Dim i as Integer     File(1) = "dept1.xlsx"     File(2) = "dept2.xlsx"     File(3) = "dept3.xlsx"     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.xlsx") End Sub

You can pass an argument to a procedure in two ways:

By reference: Passing an argument by reference (the default method) simply passes the memory address of the variable. Changes to the argument within the procedure are made to the original variable.
By value: Passing an argument by value 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

Using 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, which doesn't use an argument, is

 Public MonthVal as Integer Sub MySub() '   ... [  code goes here  ]     MonthVal = 4     Call ProcessMonth2 '   ... [  code goes here  ] End Sub

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

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 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.

Note	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, the data that is passed as the argument must match 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 .

Note

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.