Pass array to ParamArray

Question

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

Popeyed

This link suggests a way of unpacking the ParamArray when necessary, but it's basically a generalized version of your approach. Personally, I would stay away from ParamArrays if possible, favoring something like the VBA Collection class or a custom class. – Bekelja

It looks like it IS possible in VB.Net, because you can define a specific type for the ParamArray (like String in your Java example) and then you can pass "An array with the same element type as the parameter array's element type." – Bekelja

Answer 1

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

Mothering

More verbose compared to other languages, but it solves a problem that seemed unsolvable to me in VBA. Great workaround, thanks. – Popeyed

unfortunately the code does not work (anymore?) I get an err.number = 5 (invalid call or invalid argument) when in the line "arrPrms = prms" when using "Call PrAr1()" (means with an empty ParamArray at the beginning) – Labiovelar

A ParamArray() it can only be passed to another function that expects a ParamArray This is not the case. A function that expects a type of Variant will happily accept a ParamArray in it's native form (not as the first element in a new ParamArray) See @Serpentine answer, below – Raincoat

Answer 2

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

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

Serpentine

Thank you, this helped me out! Confirmed this solution does work. – Gwenngwenneth

Indeed very useful. If p1 was your original function (using ParamArray), and now you want to be able to also call it with an Array: p1 becomes p2 (only the declaration changes), and p1 is now a wrapper for p2. – Tatter

Answer 3

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.

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

Agnail

Thanks, your If IsArray(keys(0)) Then keys = keys(0) helped me pass the ParamArray on! – Guv

@Guv that's funny, I cannot use this expression as it crashes Excel! I must use Values = IIf(IsArray(Values(0)), Values(0), Values). – Wein

@t3chb0t: Excel (and Word) will crash on the "keys = keys(0)" statement if you don't first package keys as a standard Variant array, as illustrated by p1. – Agnail

Answer 4

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

Orling

Answer 5

In order to pass a ParamArray Variant from function to function called from Excel itself, the unboxing principle showed in previous posts by @JoséIborraBotia was working for a list of ranges, but catching the exception raised when trying to unbox one level to much instead of

testing VarType, UBound or IsArray like proposed previously,

allows it to also work for a single range, witch is critical when passing Excel selections to a custom function.

Let find this unboxing function along with a demonstration of its use to count any combination of Excel cells selection:

ParamArray unboxing function:

Public Function unboxPA(ParamArray prms() As Variant) As Variant
    Dim arrPrms() As Variant, arrWrk() As Variant
    Dim done As Boolean
    done = False
    arrPrms = prms
    Do While Not done
        On Error Resume Next
          arrWrk = arrPrms(0)
        If (Err.Number > 0) Then
          done = True
        End If
        arrPrms = arrWrk
    Loop
    unboxPA = arrPrms
End Function

Unboxing used to count any excel cells selction:

Function MyCountLargeCellsPA(ParamArray rangeArray() As Variant)
    Dim unboxed() As Variant
    unboxed = unboxPA(rangeArray)
    Dim n As Long
    For n = LBound(unboxed) To UBound(unboxed)
        MyCountLargeCellsPA = MyCountLargeCellsPA + unboxed(n).CountLarge
    Next
End Function

This allow any nested call from function to function using ParamArray,

It is now possible to program using VBA !

Jacobba

Answer 6

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

Bypath

Thanks for this proposal, but p2 "test", "banane", "birne" causes an error. p2 keys and p2 "test", "banane", "birne" cann't be treated equally. I've got to check, if an array is passed (see edit). But this is awkward compared to some other programming languages (e.g. Java). – Popeyed

Answer 7

One of my strongest needs is to be able to take a ParamArray values() As Variant and turned it into a String().

Per the OPs question, I also need to be able to forward other functions to this function, where the other functions have a ParamArray which needs also to be converted to a String() before that function can continue to process.

Here's the solution which includes a robust function to safely return an Array's size:

Public Function f_uas_astrFromParamArray( _
    ParamArray pr_avarValues() As Variant _
) As String()
  Dim astrResult() As String
  
  Dim avarTemp() As Variant
  Dim lngSize As Long
  Dim lngUBound As Long
  Dim lngIndex As Long
  
  If (IsMissing(pr_avarValues) = False) Then
    If (IsArray(pr_avarValues(0)) = True) Then
      avarTemp = pr_avarValues(0)
    Else
      avarTemp = pr_avarValues
    End If
    lngSize = f_lngArraySize(avarTemp)
    If (lngSize > 0) Then
      lngUBound = lngSize - 1
      ReDim astrResult(0 To lngUBound)
      For lngIndex = 0 To lngUBound
        astrResult(lngIndex) = CStr(avarTemp(lngIndex))
      Next lngIndex
    End If
  End If
  
  f_uas_astrFromParamArray = astrResult
End Function

'Return Value:
'   -1 - Not an Array
'    0 - Empty
'  > 0 - Defined
Public Function f_ua_lngArraySize( _
    ByRef pr_avarValues As Variant _
  , Optional ByVal pv_lngDimensionOneBased As Long = 1 _
) As Long
  Dim lngSize As Long: lngSize = -1 'Default to not an Array
  Dim lngLBound As Long
  Dim lngUBound As Long
  
  On Error GoTo Recovery
  
  If (IsArray(pr_avarValues) = True) Then
    lngSize = 0 'Move default to Empty
    lngLBound = LBound(pr_avarValues, pv_lngDimensionOneBased)
    lngUBound = UBound(pr_avarValues, pv_lngDimensionOneBased)
    If (lngLBound <= lngUBound) Then
      lngSize = lngUBound - lngLBound + 1 'Non-Empty, so return size
    End If
  End If
  
NormalExit:
  f_ua_lngArraySize = lngSize
  Exit Function
  
Recovery:
  GoTo NormalExit
End Function

Larianna

Answer 8

This will not help the OP anymore since the question has been asked 10 years ago in 2013.

However, if anyone runs into this issue, please do not implement such a horrible stuff blowing up the code logic (and performance) as suggested by most of the answers. Just write clean code to get around that issue.

If you actually want a ParamArray, then do that in the public interface and convert it into an ordinary Array. Let the implementation do the stuff in a straight forward way.

Option Explicit

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


'public interface

Public Sub p1(ParamArray keys() As Variant)
    Dim keys_() As Variant: keys_ = keys
    p1V keys_
End Sub

Public Sub p2(ParamArray keys() As Variant)
    Dim keys_() As Variant: keys_ = keys
    p2V keys_
End Sub

Public Sub p3(ParamArray keys() As Variant)
    Dim keys_() As Variant: keys_ = keys
    p3V keys_
End Sub


'private (or even public) implementation

Private Sub p1V(keys() As Variant)
    p2V keys
End Sub

Private Sub p2V(keys() As Variant)
    p3V keys
End Sub

Private Sub p3V(keys() As Variant)
    Dim key As Variant
    For Each key In keys
        Debug.Print key
    Next key
    ' "test", "banane", "birne"
    ' no matter which cascaded function was called
End Sub

Dovap

Answer 9

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

 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

Themselves