VBA Debug Print ParamArray Error 13 Type Mismatch Values

Question

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?

So I am obviously doing something wrong. All I need to do is print the values of my Params array to the immediate window using a for each loop. How could I do this with what I have? — tamosa
– tamosa, Commented Mar 19, 2018 at 2:03
learn.microsoft.com/en-us/dotnet/visual-basic/programming-guide/… — aaa
– aaa, Commented Mar 19, 2018 at 2:05
Looks like your working with Function, so why sub ? should be Function PrintArray(ParamArray Params() As Variant) — 0m3r
– 0m3r, Commented Mar 19, 2018 at 2:36
How are you invoking the procedure? If you're passing an array, then Params() will have 1 parameter and your loop will be iterating once, and p_param will be the array you meant to be iterating. ..and that would be a type mismatch error. — Mathieu Guindon
– Mathieu Guindon, Commented Mar 19, 2018 at 2:41

Mathieu Guindon · Accepted Answer · 2018-03-19 03:50:54Z

2

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

answered Mar 19, 2018 at 3:50

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

tamosa Over a year ago

@ Mathieu - Hey thanks for the answer it doesn't make a lot of sense just now but it is because I am not too familiar with the use of ParamArrays, rather, just arrays that I have built myself and hence have not really encountered any issues with debug printing them when I needed to. I will look into it and post back if I need any further clarity. Thanks for taking the time to respond to my question.

Mathieu Guindon Over a year ago

@tamosa basically ParamArray is building the array from whatever parameters are passed by the caller: if you pass an array, then you're looking at an array that contains an array, so you need to pull it out of the first index of the paramarrray array before you csn iterate it. BTW you should be iterating arrays with a For...Next loop, not For Each.

user8221156 · Accepted Answer · 2018-07-25 15:22:15Z

0

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

answered Jul 25, 2018 at 15:22

user8221156

Collectives™ on Stack Overflow

VBA Debug Print ParamArray Error 13 Type Mismatch Values

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related