Passing an Array to ParamArray

[brncao]

I'm trying to make a function flexible where it can accept both a ParamArray or an Array. I'm having problems with result2 (getting a mismatch error). What's the proper way to work around this?

Sub Test()
    Dim arr As Variant, result1 As String, result2 As String
    
    arr = Array("A", "B", "C")
    
    result1 = Test2("A", "B", "C")
    result2 = Test2(arr)
    
    MsgBox result1
    MsgBox result2
End Sub

Function Test2(ParamArray arg() As Variant) As String
    Dim element As Variant
    Dim str As String
    
    For Each element In arg
        str = str & element
    Next
    Test2 = str
End Function

 

[DanteAmor]

result1 = Test2("A", "B", "C")

That means you are passing 3 parameters.

Then, I think it should be like this:

Sub Test()
  Dim arr As Variant, result1 As String, result2 As String
  
  arr = Array("A", "B", "C")
  
  result1 = Test2(Array("A", "B", "C"))
  result2 = Test2(arr)
  
  MsgBox result1
  MsgBox result2
End Sub

Function Test2(arg As Variant) As String
  Dim element As Variant
  Dim str As String
  
  For Each element In arg
      str = str & element
  Next
  Test2 = str
End Function

 

[Domenic]

@brncao

In your example, your For Each/Next statement in Test2 loops through each element within Arg(). And since you're only passing a single argument, and that argument is an array, element refers to the array you've passed to your function, not an element within the array. Hence the type mismatch error.

To have your function accept any number of arguments, and with different data types, maybe something like this...

Sub Test()
    Dim arr As Variant, result1 As String, result2 As String, result3 As String, result4 As String
 
    result1 = Test2("A", "B", "C")
    MsgBox result1
 
    arr = Array("A", "B", "C")
    result2 = Test2(arr)
    MsgBox result2
 
    result3 = Test2(Range("A1:A3")) 'where the range contains "A", "B", and "C"
    MsgBox result3
 
    result4 = Test2("A", "B", "C", arr, Range("A1:A3"))
    MsgBox result4
 
End Sub

Function Test2(ParamArray arg() As Variant) As String
    Dim element As Variant
    Dim str As String
    Dim i As Long
 
    str = ""
    For i = LBound(arg) To UBound(arg)
        arg(i) = arg(i)
        If IsArray(arg(i)) Then
            For Each element In arg(i)
                str = str & element
            Next
        Else
            str = str & arg(i)
        End If
    Next i

    Test2 = str
 
End Function

Hope this helps!

 

[brncao]

Fantastic! Thank you.