CODEWITHSUNDEEP
vbams-access
tamosa
tamosa

Reputation: 77

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?

Upvotes: 3

Views: 1963

Answers (2)

user8221156
user8221156

Reputation:

If an element of the passed in Params() array is an array itself then treat it as such, otherwise just print...

Private Sub PrintArray(ParamArray Params() As Variant)

    Dim p_param As Variant
    Dim i As Long

    For Each p_param In Params
        If IsArray(p_param) Then
            For i = LBound(p_param) To UBound(p_param)
                Debug.Print p_param(i)
            Next
        Else
            Debug.Print p_param
        End If
    Next p_param

End Sub

Upvotes: 0

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

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")

Upvotes: 2

Related Questions