excel - 将数组作为 List 传递给 ParamArray

Question

我想将数组中的值传递给带有 ParamArray 的函数，就像我分别从数组中传递每个值一样。

简化示例以进一步解释我的问题：

Sub DoSomething()
    Dim MyArray(3) As Variant
    MyArray(0) = "A"
    MyArray(1) = "Whatever"
    MyArray(2) = 2
    MyArray(3) = "xyz"

    'Now I want this...
    SomeNativeFunction MyArray

    '...to act the same way like this:
    SomeNativeFunction MyArray(0), MyArray(1), MyArray(2), MyArray(3)

    '... because at this point I don't know how many elements MyArray actually might contain during runtime, so my only chance by now is something like this:
    Select UBound(MyArray)
        Case 1
            SomeNativeFunction MyArray(0)
        Case 2
            SomeNativeFunction MyArray(0), MyArray(1)
        Case 3
            SomeNativeFunction MyArray(0), MyArray(1), MyArray(2)
        Case .....
        Case 99999
            SomeNativeFunction MyArray(0), MyArray(1), MyArray(2), MyArray(...)
    End Select
End Sub

Function SomeNativeFunction (ParamArray args() As Variant)
    'can't touch this...
End Function

SomeNativeFunction上例中我一开始调用的方式MyArray会交给 0 中的位置args，所以args(0) = MyArray. 但相反，我需要args(0) = MyArray(0),args(1) = MyArray(1)等。就像我在第二个函数调用中所做的那样。问题是，MyArray 可能有不同数量的参数，因此硬编码实际上并非在所有情况下都有效，或者至少会变得非常混乱。

我想以这种方式调用的 subs/functions 由 Excel 或其他来源提供，因此无法更改它们。

标签： excelvbaparamarray