What is the benefit of using ParamArray (vs a Variant array)?

Question

I've used the ParamArray statement for years when I wanted to accept a variable number of arguments. One good example is this MinVal function:

Function MinVal(ParamArray Values() As Variant)
    Dim ReturnVal As Variant, v As Variant

    If UBound(Values) < 0 Then
        ReturnVal = Null
    Else
        ReturnVal = Values(0)
        For Each v In Values
            If v < ReturnVal Then ReturnVal = v
        Next v
    End If
    MinVal = ReturnVal

End Function
' Debug.Print MinVal(10, 23, 4, 17)
' 4

This could be re-written without the ParamArray as:

Function MinVal(Optional Values As Variant)
    Dim ReturnVal As Variant, v As Variant

    If IsMissing(Values) Or IsNull(Values) Then
        ReturnVal = Null
    Else
        ReturnVal = Values(0)
        For Each v In Values
            If v < ReturnVal Then ReturnVal = v
        Next v
    End If
    MinVal = ReturnVal

End Function
' Debug.Print MinVal(Array(10, 23, 4, 17))
' 4

Note in the second example use of the Array() function in the call to MinVal.

The second approach has the advantage of being able to pass the parameter array to another function that also accepts arrays. This provides flexibility if I ever wanted to be able to pass the parameter array in MinVal on to some other function.

I've begun thinking I should always favor this approach and just stop using ParamArray altogether.

One could argue that using ParamArray makes for more explicitly readable code. However, there's no advantage in compile-time checks because ParamArray must be an array of Variants. Can anyone offer a compelling reason to ever use ParamArray?

`Array` function returns array of `Variant`s too -- and you can't cast it to array of `Long`s as is. — wqw, Mar 17 '14 at 21:14

score 3 · Answer 1 · answered Mar 17 '14 at 21:16

3

Most of my ParamArray functions have std Array versions that do the heavy lifting like this:

Private Sub Command2_Click()
    Process 1, 2, 3
End Sub

Private Sub Process(ParamArray A() As Variant)
    ProcessArray CVar(A)
End Sub

Private Sub ProcessArray(B As Variant)
    Debug.Print UBound(B)
End Sub

This does not work for output params though, so yes replacing ParamArray with Array gets really fast very inconvenient for output params.

answered Mar 17 '14 at 21:16

wqw

11,771
1
33
41

1

I also do that - have an "array" version that can be called directly, and sometimes a `ParamArray` version that delegates to the "array" version. Also, I never knew that `ParamArray` parameters were essentially passed `ByRef`. While I think using elements of a `ParamArray` as output parameters is likely to be very dangerous, it's nice to learn something new. – jtolle Mar 22 '14 at 21:46

score 2 · Answer 2 · answered Mar 22 '14 at 21:44

You've already noted one reason to use Paramarray - sometimes it makes code more clear. In fact, I think in your own example that:

Debug.Print MinVal(10, 23, 4, 17)

is preferable to:

Debug.Print MinVal(Array(10, 23, 4, 17))

Of course, how "compelling" this is is a matter of opinion. Code clarity rarely matters much in small examples, but in a large code base it can add up to be very important.

If you're using VBA with Excel, then there is a somewhat-compelling reason to use ParamArray. Consider a UDF of the following form:

Public Function mungeRanges(ParamArray ranges())
    'do something with a bunch of ranges
End Function

The built-in Excel function MIN works like this in fact - you can pass in multiple arguments, including ranges or arrays, and it looks through all of them to find the smallest individual value. Anything that follows a similar pattern would need to use ParamArray.

What is the benefit of using ParamArray (vs a Variant array)?

2 Answers2

Linked