Page 115 - Excel Progamming Weekend Crash Course

h540629 ch07.qxd 9/2/03 9:33 AM Page 90

90 Saturday Morning

ParamArray Arguments

Additional flexibility in passing arguments to procedures is provided by the ParamArray
keyword. Here’s the syntax:

Sub SubName(ParamArray ArrayName())
This specifies that an array of arguments will be passed to the procedure. The array is
always type Variant, but its length is flexible. Here’s how it works. Suppose a procedure
has been defined as follows:
Sub MySub(ParamArray Data())
...
End Sub

The procedure is then called as follows:
Call MySub(“hello”, 55, #12/25/2003#)
Within the procedure, the array Data contains three elements, with Data(0) containing
the string “hello,” Data(1) containing the value 55, and Data(2) containing the date
12/25/2003. The array size depends on the number of arguments passed. Use the UBound
function to determine the size of the array.
You can combine ParamArray arguments with regular required arguments. There can be
only one ParamArray in a procedure definition, and it must be the last item in the argu-
ment list. You cannot use the Optional keyword with ParamArray. Here’s an example:

Sub MySub(A As Integer, B As String, ParamArray pa())
Now the procedure is called as follows:
Call MySub(“first”, “second”, “third”, “fourth”)

The values “first” and “second” are passed as the arguments A and B. The values “third”
and “fourth” are passed in the array pa().

ByVal and ByRef Arguments
The terms ByVal and ByRef refer to the two ways in which a variable can be passed to a
procedure as an argument. ByRef, which stands for “by reference,” is the default. In this
case, a reference to the argument is passed to the procedure — in other words, a pointer
to where the argument is stored in memory. For example:

Dim str As String
str = “hello”
MySub str
Here, it is the memory address of the variable str that is passed to the procedure. Having
this address, code in the procedure can access the value of str. Furthermore, code in the
procedure can modify the value of str. This means that if code in the procedure changes
the argument, the change is reflected in the original variable. Here’s a demonstration: