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.

Answer 3

There is no problem with returning typed arrays from functions or passing typed arrays to functions as arguments. The following works as expected:

Option Explicit

Sub asdfasf()
  Dim DMAs() As String

  DMAs = CreateAnArray()
  OutputAnArray DMAs
End Sub

Private Function CreateAnArray() As String()
  Dim arr() As String
  ReDim arr(1 To 5)

  Dim i As Long
  For i = LBound(arr) To UBound(arr)
    arr(i) = i
  Next

  CreateAnArray = arr
End Function

Private Sub OutputAnArray(ByRef arrayToOutput() As String)
  Dim i As Long
  For i = LBound(arrayToOutput) To UBound(arrayToOutput)
    Debug.Print arrayToOutput(i)
  Next
End Sub

Now, you never show how you actually pass the DMAs array to OutputAnArray.

I'm willing to make an educated guess that you are doing

OutputAnArray (DMAs)

which will indeed result in

Type mismatch: array or user-defined type expected

You cannot freely put parentheses in that manner. They have special meaning.
If you want parentheses to be used when calling a sub, you must use Call:

Call OutputAnArray(DMAs)

And if you don't care, omit the parentheses like in the example above:

OutputAnArray DMAs

Answer 4

I had the same error while passing an array (of user defined type) as an argument to a function ByRef.

In my case the problem was solved using the keyword "Call" in front of the function or the sub being called.

I don't really understand it, but to me it seems like VBA is trying to interpret the function/sub a couple of different ways in the absence of "Call" - which leads to the error message.

I personally try to avoid converting anything to a variant as long as possible.