Microsoft Office Tutorials and References

In Depth Information

=USER(False)

=USER(True)

Using an indefinite number of arguments

Some of the Excel worksheet functions take an indefinite number of arguments. A familiar example is the SUM

function, which has the following syntax:

SUM(number1,number2...)

The first argument is required, but you can have as many as 254 additional arguments. Here's an example of a

formula that uses the SUM function with four range arguments:

=SUM(A1:A5,C1:C5,E1:E5,G1:G5)

You can mix and match the argument types. For example, the following example uses three arguments — a

range, followed by a value, and finally an expression:

=SUM(A1:A5,12,24*3)

You can create Function procedures that have an indefinite number of arguments. The trick is to use an array as

the last (or only) argument, preceded by the keyword ParamArray.

ParamArray can apply only to the last argument in the procedure. It is always a variant

data type, and it is always an optional argument (although you don't use the Optional

keyword).

A simple example of indefinite arguments

The following is a Function procedure that can have any number of single-value arguments. It simply returns

the sum of the arguments.

Function SIMPLESUM(ParamArray arglist() As Variant) As Double

Dim arg as Variant

For Each arg In arglist

SIMPLESUM = SIMPLESUM + arg

Next arg

End Function

The following formula returns the sum of the single-cell arguments:

=SIMPLESUM(A1,A5,12)