Compile error: Type mismatch: array or user-defined type expected

[vxs8122]

I kept getting this error and I am unsure what to do. This error pinpoints to the CollectUniqueYear() function. Has to do something with data type mismatch, but CollectUniqueYears() should return a variant array type and AddListBox should accept variant array type.

What this code does is setting up a listbox with a list of unique years collected from the date column of the table.

Sub Setup()

'   ....


    column = "A"
    startingRow = 11
    Dim Year0(0) As Variant
    Year0(0) = "(Select All)"
    Call AddListBox(column & startingRow + 1, Year0, "YearListBoxAll", "CheckBoxes")
    Call AddListBox(column & startingRow + 2, CollectUniqueYears(), "YearListBox", "CheckBoxes", 3)

'   ...

End Sub

Private Sub AddListBox(Address As String, ByRef arr() As Variant, Name As String, Optional ListStyle As String, Optional Rows As Variant = "Default")
    
    Dim MyHeight As Double
    Dim objListBox As OLEObject
    
    With Range(Address)
        If Rows = "Default" Then
            Height = .Height * Application.CountA(arr)
        Else
            Height = .Height * Rows
        End If
        Set objListBox = ActiveSheet.OLEObjects.Add( _
            ClassType:="Forms.ListBox.1", _
            Left:=.Left, _
            Top:=.Top, _
            Height:=Height, _
            Width:=.Width)
    End With
        
    With objListBox
        .Name = Name
        .Placement = 3
        .Object.BorderStyle = 1
        .Object.List = arr
        .Object.ListStyle = 1
        If ListStyle = "CheckBoxes" Then
            .Object.MultiSelect = fmMultiSelectMulti
        End If
    End With


End Sub

Private Function CollectUniqueYears() As Variant
    Dim tmp As String
    Dim ArrayOfYears() As String
    
    Set rngDate = ActiveSheet.ListObjects("Table1").ListColumns("Date").DataBodyRange
    
    If Not rngDate Is Nothing Then
       For Each cell In rngDate
          If (cell <> "") And (InStr(tmp, Year(cell)) = 0) Then
            tmp = tmp & Year(cell) & "|"
          End If
       Next cell
    End If


    ArrayOfYears = Split(tmp, "|")
    CollectUniqueYears = ArrayOfYears
End Function

[mancubus]

check if all the values in Date column of the table are valid dates.

[vxs8122]

check if all the values in Date column of the table are valid dates.

Pretty sure they are valid dates.

I tested the CollectUniqueYears() function with this test subroutine:

Sub Test()
    MsgBox Join(CollectUniqueYears, ",")
End Sub

And it displayed "1970,1971, ... , 1979". The dates on the date columns are in 1970s.

[mancubus]

then upload your workbook (replace your data with fake data but preserve data types in colums).

perhaps one of our members can assist you in solving the problem.

[vxs8122]

Just found out why and fixed it. The CollectUniqueYears() returns variant array, but its elements are still string because the split function returns string. So I added a loop to convert every element in the array to variant and it works now.

It's strange how arrays works in VBA.