Microsoft Office Tutorials and References
In Depth Information
Passing Arguments to Procedures
h An array
h An object
The use of arguments by procedures is very similar to their use of worksheet functions in the
h A procedure may not require any arguments.
h A procedure may require a fixed number of arguments.
h A procedure may accept an indefinite number of arguments.
h A procedure may require some arguments, leaving others optional.
h 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 topic 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.
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
‘ ...[more code here]...