A function with an indefinite number of arguments

Some Excel worksheet functions take an indefinite number of arguments. A familiar example is the SUM function, which has the following syntax:


The first argument is required, but you can have as many as 29 additional arguments. Here's an example of a SUM function with four range arguments:

You can even mix and match the argument types. For example, the following example uses three arguments: the first is a range, the second is a value, and the third is an expression.

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's argument list. It is always a Variant data type, and it is always an optional argument (although you don't use the Optional keyword).

Following is a function that can have any number of single-value arguments. (It doesn't work with multicell range arguments.) It simply returns the sum of the arguments.

Function SimpleSum(ParamArray arglist() As Variant) As Double For Each arg In arglist

SimpleSum = SimpleSum + arg Next arg End Function

The SimpleSum function is not nearly as flexible as Excel's SUM function. Try it out by using various types of arguments, and you'll see that it fails unless each argument is either a value or a reference to a single cell that contains a value.

0 0

Post a comment