vbaparametersparameter-passingparamarray

Pass array to ParamArray


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

For example I'd like to pass a ParamArray to another 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 p2 doesn't contain the elements of keys, but it gets the array-object keys. Thus I've got to check, if an arrays is passed:

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:

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.

Thanks for help,
Michael


Solution

  • 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 String the arguments received in other function in a ParamArray passing directly without explicitly write. The restrictions I've found are:

    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.

    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