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.