Unable to add type defined variable to collection

[Sorwen]

I'm trying a collection and when I try to add the data type I created to the collection "SheetCollection.Add (SheetData)" I get the error "Only user-defined types defined in public object modules can be coerced to or from a variant or passed to late-bound functions".

Public Function LoadCollection(SheetName As String, CompareColumn As String, Optional SecondCompareColumn As String) As Collection
Dim LastRowSheet As Long
Dim LoadCount As Long
Dim MarkedColor As Integer
Dim MyRange As String, MyRange2 As String
Dim SheetData As LineData
Dim SheetCollection As New Collection

LastRowSheet = CountRows(SheetName)

ProgressBar_Form.Set_ProgressBar_Outer 1, LastRowSheet
For LoadCount = 1 To LastRowSheet
    MyRange = CompareColumn & LoadCount
    MarkedColor = Sheets(SheetName).Range(MyRange).Interior.ColorIndex
    With SheetData
        .DataString1 = Sheets(SheetName).Range(MyRange).Value
        .OnRow = LoadCount
        If MarkedColor <> NoColor Then
            .MarkedColor = MarkedColor
        Else
            .MarkedColor = NoColor
        End If
        If SecondCompareColumn <> "" Then
            MyRange2 = SecondCompareColumn & LoadCount
            .DataString2 = Sheets(SheetName).Range(MyRange2).Value
        End If
    End With
    SheetCollection.Add (SheetData)
    
    ProgressBar_Form.Update_ProgressBar_Outer LoadCount, "Loading " & LoadCount & _
                                    "/" & LastRowSheet & _
                                    Chr(13) & "Please Wait....."
Next LoadCount
ProgressBar_Form.Hide

LoadData = SheetCollection

End Function

There is also an error when I try to pass the collection back to the calling function. The error is "Function Call on left-hand side of assignment must return variant or object". But a collection is an object?

 

[PHV]

Replace this:
SheetCollection.Add (SheetData)
with this:
SheetCollection.Add SheetData
And this:
LoadData = SheetCollection
By this:
Set LoadCollection = SheetCollection

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Sorwen]

Thank you, but I've tried both changes and I get the same error on the SheetCollection and I get "Argument not optional" when I add the Set.