excel - 如何打开带有范围，单个单元格和/或直接输入到数组的ParamArray？

我正在尝试构建一个将接受一个ParamArray的UDF，该参数将接受一个单元格，一系列单元格和/或一个直接值。然后，我需要遍历ParamArray以获取所有传递的值，但是当我尝试ReDim数组时，它将卡住并且循环结束。我只是在做这件事完全错误，因为这似乎工作太多。

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

最佳答案

您有二维数组ReDim argsarray(0, 0)。

如来自MSDN的跟进：


如果使用Preserve，则只能调整尺寸的最后一个尺寸
数组。对于其他所有维度，您必须指定
现有数组。


因此，这在ReDim Preserve argsarray(0 To UBound(argsarray) + 1)中不起作用，但这一方法在ReDim Preserve argsarray(0, 0 To UBound(argsarray) + 1)中起作用。

顺便说一句，Preserve是非常昂贵的操作。我建议您在对所有单元格进行计数之后，查看代码并使用单个ReDim argsarray

尝试以下一项：

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

如果UDF的目的仅是连接值，则也可以使用此值：

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