Microsoft Office Tutorials and References
In Depth Information
Function Examples
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:
SUM(number1,number2...)
The first argument is required, but you can specify as many as 254 additional arguments. Here’s
an example of a SUM function with four range arguments:
=SUM(A1:A5,C1:C5,E1:E5,G1:G5)
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.
=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’s argument list. It’s
always a Variant data type, and it’s 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
To modify this function so that it works with multicell range arguments, you need to add another
loop, which processes each cell in each of the arguments:
Function SimpleSum(ParamArray arglist() As Variant) As Double
Dim cell As Range
For Each arg In arglist
For Each cell In arg
SimpleSum = SimpleSum + cell
Next cell
Next arg
End Function                                Search JabSto ::

Custom Search