Page 72 - Excel for Scientists and Engineers: Numerical Methods

CHAPTER 2 FUNDAMENTALS OF PROGRAMMING WITH VBA 49

Passing an Indefinite Number of Arguments
Using the ParamArray Keyword

Occasionally a Function procedure needs to accept an indefinite number of
arguments. The SUM worksheet function is an example of such a function; its
syntax is =SUM(numberl ,number2,. . .). To enable a Function procedure to
accept an indefinite number of arguments, use the ParamArray keyword in the
argument list of the function, as in the following expression
Function ArrayMaker( ParamArray rng())
Only one argument can follow the ParamArray keyword, and it must be the
last one in the function's list of arguments. The argument declared by the
ParamArray keyword is an array of Variant elements. Empty parentheses are
required.
The lower bound of the array is zero, even if you have used the Option Base
I statement. Use UBound(rng) to find the upper array index.
Elements in the array of arguments passed using the ParamArray keyword
can themselves be arrays. The following code illustrates how to access
individual elements of each array in an array of elements passed using
ParamArray.

Function ArrayMaker(ParamArray rng())

For J = 0 To UBound(rng)
YSize = rng(J).Columns.Count
For K = 1 To YSize
statements
Next K
Next J
Figure 2-18. Handling an array of array arguments passed by using ParamArray.

Returning an Array of Values as a Result
The most obvious way to enable a Function procedure to return an array of
values is to assemble the values in an array and return the array. The procedure
shown in Figure 2-19 illustrates a function that returns an array of three values.
To use the function, the user must select a horizontal range of three cells, enter
the function and press CONTROL+SHIFT+ENTER.