VBA "Type mismatch: array or user-defined type expected” on String Arrays

Question

I have a dynamic array of strings DMAs which I declare globally.

Dim DMAs() As String

I ReDim the array and assign values to it in the CreateArrayOf function which is of type String() that returns an array of type String()

DMAs = CreateArrayOf(Sites, 2, "", False) 

Public Function CreateArrayOf( _
    ByRef arrayFrom() As String, _
    Optional ByVal numOfChars As Integer = 2, _
    Optional ByVal filterChar As String = "", _
    Optional ByVal filterCharIsInteger As Boolean = False _
) As String()

Dim i As Integer, _
    j As Integer, _
    strn As Variant, _
    switch As Boolean, _
    strArray() As String

'numOfChars 2 for DMA with no filterChar
'numOfChars 3 for W with filterChar "W"
'numOfChars 3 for A with filterChar "A"
'numofChars 2 for D with filterChar "D"

ReDim strArray(LBound(arrayFrom) To LBound(arrayFrom))  'required in order to
'not throw error on first iteration

For i = LBound(arrayFrom) To UBound(arrayFrom)  'iterate through each site
switch = False

For Each strn In strArray 'iterate through the array to find whether the
'current site already exists
     If strn = Mid(arrayFrom(i), 1, numOfChars) And Not strn = "" Then
        switch = True
    End If
Next strn

If switch = False Then 'if it doesn't exist add it to the array
    ReDim Preserve strArray(1 To UBound(strArray) + 1)
    strArray(UBound(strArray) - 1) = Mid(arrayFrom(i), 1, numOfChars)
End If
Next i

CreateArrayOf = strArray 'return the new array
End Function

When I attempt to pass the DMAs array to another function OutputAnArray

Private Sub OutputAnArray(ByRef arrayToOutput() As String)

Dim i As Variant
Dim x As Integer
x = 1
For Each i In arrayToOutput
    Cells(x, 6).Value = i
    x = x + 1
Next i

End Sub

I get the "Type mismatch: array or user-defined type expected". Throughout the whole process I only mess with string arrays.

If I take the content of the OutputAnArray function and put it in the parent function where I'm calling it from, everything's fine.

Any help is appreciated.

Answer 1

I changed all String definitions to Variants

Private Sub OutputAnArray(ByRef arrayToOutput() As Variant)

The culprit was still there, so then after a whole lot of attempts to get this to compile, I removed the () from the arrayToOutput parameter and it started working.

Private Sub OutputAnArray(ByRef arrayToOutput As Variant) 'fixed

What is still perplexing is the fact that in the following function definition, the () are needed for arrayFrom.

Public Function CreateArrayOf(ByRef arrayFrom() As Variant, _ ...

I really don't get it, if anyone has any idea of an explanation, I'd love to hear it.

Answer 2

From the documentation:

"Arrays of any type can't be returned, but a Variant containing an array can."

If follows that the function "CreateArrayOf" does not return an array of strings: it returns a variant containing an array of strings.

The variant cannot be passed as a parameter to a function expecting an array of strings:

Private Sub OutputAnArray(ByRef arrayToOutput() As String) 

It can only be passed to a function expecting a variant:

Private Sub OutputAnArray(ByRef arrayToOutput as Variant) 

Conversely, DMA is an array of strings:

Dim DMAs() As String

DMA can be passed to a function expecting an array of strings:

Public Function CreateArrayOf(ByRef arrayFrom() As String, _ .

And finally, "Type mismatch: array or user-defined type expected" is a generic type mismatch message. When you pass an array of the wrong type, or a variant array, and get the error "array expected", it's not particularly helpful.