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