VBA Debug Print ParamArray Error 13 Type Mismatch Values

In Access VBA, I am trying to print the values of a parsed Parameter array but keep getting a Runtime Error 13 - Type Mismatch. The values in the array are mixed types i.e. Double, String, Long.

Code as follows:

Function MyArray() as Variant

Dim MyParams(2) as Variant
MyParams(0) = "3459"
MyParams(1) = "3345"
MyParams(2) = "34.666"

MyArray = MyParams

End Function

Sub PrintArray(ParamArray Params() As Variant)

    Dim p_param as Variant

    For Each p_param in Params
        Debug.Print params < - Error occurs here
    Next p_param

End Sub

I tried converting to string etc but it still wont work.

Any suggestions?

In order to iterate the ParamArray values, you need to understand what arguments you're receiving.

Say you have this:

Public Sub DoSomething(ParamArray values() As Variant)

The cool thing about ParamArray is that it allows the calling code to do this:

DoSomething 1, 2, "test"

If you're in DoSomething, what you receive in values() is 3 items: the numbers 1 & 2, and a string containing the word test.

However what's happening in your case, is that you're doing something like this:

DoSomething Array(1, 2, "test")

And when you're in DoSomething, what you receive in values() is 1 item: an array containing the numbers 1 & 2, and a string containing the word test.

The bad news is that you can't control how the calling code will be invoking that function.

The good news is that you can be flexible about it:

Public Sub DoSomething(ParamArray values() As Variant)
    If ArrayLenth(values) = 1 Then
        If IsArray(values(0)) Then
            PrintArray values(0)
        End If
    Else
        PrintArray values
    End If
End Sub

Public Function ArrayLength(ByRef target As Variant) As Long
    Debug.Assert IsArray(target)
    ArrayLength = UBound(target) - LBound(target) + 1
End Function

Now either way can work:

DoSomething 1, 2, "test"
DoSomething Array(1, 2, "test")