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.

Colunion

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
Advertisements

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).

    http://www.mrexcel.com/forum/showthread.php?t=507096


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




June 2009
M T W T F S S
« May   Jul »
1234567
891011121314
15161718192021
22232425262728
2930  

%d bloggers like this: