How can I turn a ParamArray with ranges, individual cells and/or direct inputs to an Array?

Question

I am trying to build a UDF that will accept a ParamArray that will accept one cell, a range of cells, and/or a direct value. I need to then loop through the ParamArray to get all the values passed, but when I try to ReDim an array, it gets stuck and the loop ends. Am I just doing this completely wrong because this just seems like too much work.

Function myudf(first As Variant, ParamArray args() As Variant)
    Dim i, j As Long
    Dim argsarray() As Variant
    ReDim argsarray(0, 0)

    For i = 0 To UBound(args)
        If Not IsObject(args(i)) Then
            ReDim Preserve argsarray(0 To UBound(argsarray) + 1)
            argsarray(i) = args(i) ' Handle explicitly passed arguments, e.g. "1,goonie,etc")

        ElseIf IsArray(args(i).Value2) Then
            For j = 1 To UBound(args(i).Value2)
                ReDim Preserve argsarray(0 To UBound(argsarray) + 1)
                argsarray(UBound(argsarray) + 1) = args(j).Value2 ' Handle a range of cells, e.g. A1:A3
            Next j
        Else
            ReDim Preserve argsarray(0 To UBound(argsarray) + 1)
            argsarray(i) = args(i).Value2 ' Handle individual cells, e.g. A1)
        End If
    Next i

    myudf = Join(argsarray, ",")

End Function

Dmitry Pavliv Dmitry Pavliv · Accepted Answer · 2014-03-22T21:54:11

You have two-dimmentsion array ReDim argsarray(0, 0).

As follow up from MSDN:

If you use Preserve, you can resize only the last dimension of the array. For every other dimension, you must specify the bound of the existing array.

So, this doesn't work ReDim Preserve argsarray(0 To UBound(argsarray) + 1), but this one works ReDim Preserve argsarray(0, 0 To UBound(argsarray) + 1).

Btw, Preserve is very expensive operation. I suggets you to review your code and use single ReDim argsarray after counting all cells

Try this one instead:

Function myudf(first As Variant, ParamArray args() As Variant)
    Dim i As Long, cnt As Long
    Dim argsarray() As Variant

    Dim c, rng

    For Each rng In args
        If IsObject(rng) Then
            cnt = cnt + rng.Cells.Count
        ElseIf IsArray(rng) Then
            cnt = cnt + UBound(rng)
        Else
            cnt = cnt + 1
        End If
    Next

    ReDim argsarray(1 To cnt)

    i = 1
    For Each rng In args
        If IsObject(rng) Or IsArray(rng) Then
            For Each c In rng
                argsarray(i) = c
                i = i + 1
            Next
        Else
            argsarray(i) = rng
            i = i + 1
        End If
    Next

    myudf = Join(argsarray, ",")
End Function

If the purpose of UDF is only to join values, you can use this one as well:

Function myudf2(first As Variant, ParamArray args() As Variant)       
    Dim c, rng

    For Each rng In args
        If IsObject(rng) Or IsArray(rng) Then
            For Each c In rng
                myudf2 = myudf2 & c & ","
            Next
        Else
            myudf2 = myudf2 & rng & ","
        End If
    Next

    myudf2 = Left(myudf2, Len(myudf2) - 1)
End Function

How can I turn a ParamArray with ranges, individual cells and/or direct inputs to an Array?

1 Answers