vba 将数组传递给 ParamArray

Pass array to ParamArray

提问by maiermic

is it possible to pass all elements of an array to a ParamArray?

是否可以将数组的所有元素传递给 ParamArray？

For example I'd like to pass a ParamArray to another ParamArray:

例如，我想将一个 ParamArray 传递给另一个 ParamArray：

Sub test()
    p1 "test", "banane", "birne"
End Sub

Sub p1(ParamArray keys() As Variant)
    p2 keys 'should be the same as: p2 "test", "banane", "birne"
End Sub

Sub p2(ParamArray keys() As Variant)
    Dim key As Variant
    For Each key In keys
        Debug.Print key 'Run-time error '13' Type mismatch (key is an array)
    Next key
End Sub

In this case ParamArray of p2doesn't contain the elements of keys, but it gets the array-object keys. Thus I've got to check, if an arrays is passed:

在这种情况下， ParamArray ofp2不包含 的元素keys，但它获取 array-object keys。因此，我必须检查是否传递了数组：

Sub test()
    p1 "test", "banane", "birne"
    p2 "test", "banane", "birne"
End Sub

Sub p1(ParamArray keys() As Variant)
    p2 keys
End Sub

Sub p2(ParamArray params() As Variant)
    Dim keys As Variant
    If IsArray(params(0)) Then
        keys = params(0)
    Else
        keys = params
    End If

    Dim key As Variant
    For Each key In keys
        Debug.Print key
    Next key
End Sub

But this is awkward for example compared to Java:

但这很尴尬，例如与 Java 相比：

public class VarArgs {

    public static void main(String[] args) {
        p1("test", "banane", "birne");
        p2("test", "banane", "birne");

        String[] array = {"test", "banane", "birne"};
        p1(array);
        p2(array);
    }

    public static void p1(String... strings) {
        p2(strings);
    }

    public static void p2(String... strings) {
        for (String string : strings) {
            System.out.println(string);
        }
    }

}

In Java I don't have to distinguish. But this is probably not possible in VBA.

在 Java 中，我不必区分。但这在 VBA 中可能是不可能的。

Thanks for help,
Michael

感谢您的帮助，
迈克尔

采纳答案by José Iborra Botia

Pass a ParamArray argument to another function that expects a ParamArray argument (delegate ParamArray arguments). I need to delegate to a function of type: strf(str as string, ParamArray args() as Variant) as Stringthe arguments received in other function in a ParamArray passing directly without explicitly write. The restrictions I've found are:

将 ParamArray 参数传递给另一个需要 ParamArray 参数的函数（委托 ParamArray 参数）。我需要委托给一个类型的函数：strf(str as string, ParamArray args() as Variant) as String在 ParamArray 中的其他函数中接收的参数直接传递而无需显式写入。我发现的限制是：

1. A ParamArray() it can only be passed to another function that expects a ParamArray.
2. The ParamArray is received at element 0 as a Variant ()
3. When the second function receives it increases a level of depth I have not found any satisfactory solution, but I have written a function that works perfectly, undoing the depth levels added and returning a vector with arguments received.

1. ParamArray() 它只能传递给另一个需要 ParamArray 的函数。
2. ParamArray 在元素 0 处作为 Variant () 接收
3. 当第二个函数接收到它增加了一个深度级别时，我没有找到任何令人满意的解决方案，但我编写了一个完美运行的函数，撤消添加的深度级别并返回一个带有接收到的参数的向量。

Code:

代码：

Option Explicit
Option Base 1

Public Sub PrAr1(ParamArray pa1() As Variant)
Dim arr() As Variant
  arr = fn.ParamArrayDelegated(pa1)
  PrAr2 pa1
End Sub

Public Sub PrAr2(ParamArray pa2() As Variant)
Dim i As Integer, arrPrms() As Variant
  arrPrms = fn.ParamArrayDelegated(pa2)
  For i = 0 To UBound(arrPrms)
    Debug.Print s.strf("i: %0 prm: %1 ", i, arrPrms(i))
  Next i
  PrAr3 pa2
End Sub

Public Sub PrAr3(ParamArray pa3() As Variant)
Dim i As Integer, arrPrms() As Variant
  arrPrms = fn.ParamArrayDelegated(pa3)
  For i = 0 To UBound(arrPrms)
    Debug.Print s.strf("i: %0 prm: %1 ", i, arrPrms(i))
  Next i
End Sub

Public Function ParamArrayDelegated(ParamArray prms() As Variant) As Variant
Dim arrPrms() As Variant, arrWrk() As Variant
'When prms(0) is Array, supposed is delegated from another function
  arrPrms = prms
  Do While VarType(arrPrms(0)) >= vbArray And UBound(arrPrms) < 1
    arrWrk = arrPrms(0)
    arrPrms = arrWrk
  Loop
  ParamArrayDelegated = arrPrms
End Function

回答by user3598756

you could convert it into a Variantfrom the 2nd call on:

您可以将其Variant从第二次调用转换为：

Sub test()
    p1 "test", "banane", "birne"
End Sub

Sub p1(ParamArray keys() As Variant)
    p2 CVar(keys) '<--| pass it as a Variant
End Sub

Sub p2(keys As Variant) '<--| accept a Variant argument
    Dim key As Variant

    For Each key In keys
        Debug.Print key
    Next key
End Sub

回答by pstraton

Here's my solution. Note that Its one restriction is that you can pass only one (Variant) array argument to the ParamArray parameter set. Possibly it could be generalized to handle multiple passed arrays but I have yet to run into that need.

这是我的解决方案。请注意，它的一个限制是您只能将一个（变体）数组参数传递给 ParamArray 参数集。可能它可以推广到处理多个传递的数组，但我还没有遇到这种需求。

Option Explicit

Sub test()
    p1 "test", "banane", "birne"
    p2 "test", "banane", "birne"
End Sub


Sub p1(ParamArray keys() As Variant)
    Dim TempKeys As Variant

    TempKeys = keys 'ParamArray isn't actually a standard Variant array, so you have to copy
                    'it to one in order for the added test/workaround in p2 to not crash
                    'Excel.

    p2 TempKeys 'should be the same as: p2 "test", "banane", "birne"
End Sub

Sub p2(ParamArray keys() As Variant)
    Dim key As Variant

    If IsArray(keys(0)) Then keys = keys(0) 'Set this routine's ParamArray parameter to be
                                            'the array of its first element.

    For Each key In keys
        Debug.Print key
    Next key
End Sub

回答by gitti

Try:

尝试：

Sub p2(ParamArray keys() As Variant) 
dim myKey as Variant  
 If IsArray(keys(0)) Then
        myKey = keys(0)
    Else
        myKey = keys()
 End If

...
end sub

回答by Brain

Sub test()
    p1 "test", "banane", "birne"
End Sub

Sub p1(ParamArray keys() As Variant)
    p2 keys
End Sub

Sub p2(ParamArray keys() As Variant)
    Dim key As Variant
    For Each key In keys
        Debug.Print key(0) '<- Give an Index here.
    Next key
End Sub

回答by storm_88

paramArrays are weird but you can use normal Array, which works just fine

paramArrays 很奇怪，但你可以使用普通的 Array，它工作得很好

 Sub test()
    Dim a As Variant: a = Array("test", "banane", "birne")
    p1 a
End Sub

Sub p1(keys As Variant)
    p2 keys
End Sub

Sub p2(keys As Variant)
    Dim key As Variant
    For Each key In keys
        Debug.Print key
    Next key
End Sub