### Parameter Arrays

I’ve been looking for an example of using Parameter Arrays: that is, the ability for a procedure to take an arbitrary number of arguments. Incidentally, is there a limit?

This is most familiar with worksheet functions, such as SUM:

=SUM(b4:b6,d8:d9,f12)

where multiple ranges can be specified in the formula.

If you were implementing an equivalent function, you would use the ParamArray keyword:

```Public Function MYSUM(ParamArray inranges() As Variant) _
As Double```

where the parameter is an array of Variants (always).

So I was wondering how people use this feature, if at all.

If the procedure with the ParamArray is called from code, then you would expect it to be passed an array or Collection – in other words, the input values are already in some kind of structure.

It makes more sense for a formula function, because the formula-writer can then choose an arbitrary set of inputs, one at a time. Could these be anything other than Ranges?

Anyway, I was trying to think of a multiple-Range function that might be useful, and I thought of a function that takes multiple ranges and combines them all into a single column. Let’s call it COLUNION (Union into a single Column). Rather like TRANSPOSE, you use it in an array function over the target Range.

I5:I13 contain the array formula:

{=COLUNION(B4:B6,D8:E9,F12:G12)}

You could use Range names instead, of course. The formula is completely ‘live’, so changes to the input ranges propagate immediately.

The main drawback is that, as with many array formulas, you have to get the size of the target range right. Note also that, as visible in Range B, the cells are retrieved by column, then row (so …,4,6,5,7,…).

Here’s the code:

```Public Function COLUNION(ParamArray inranges() As Variant) _
As Variant
Dim outvalues() As Variant

'Work out the size needed for outvalues
Dim ncells As Long
Dim i As Integer
For i = LBound(inranges) To UBound(inranges)
ncells = ncells + inranges(i).Count
Next
ReDim outvalues(1 To ncells, 1 To 1) As Variant

Dim j As Long
j = 1
Dim rng As Range
For i = LBound(inranges) To UBound(inranges)
Set rng = inranges(i)
Dim cell As Range
For Each cell In rng
outvalues(j, 1) = cell.value
j = j + 1
Next
Next

COLUNION = outvalues
End Function```

#### 1 Response to “Parameter Arrays”

1. 1 Ron Memke December 2, 2010 at 8:58 pm

ParamArray turned out to be what I needed for my question in this thread (repeatedly calling the same sub on a list of dozens of arrays, individually, without having that many lines of code to call the sub–paramarray allows me to send as many arguments to act on as I want, looping through them _within_ the sub).