Error with User-defined type and function

[Sorwen]

I need help with this error. I created a type for working with rows of data in excel. I've tried this type in both a seperate module and in the same module and I keep getting 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 Type LineData
    DataString1 As String
    DataString2 As String
    MarkedColor As Integer
    OnRow As Long
    Found As Boolean
End Type

Function

Public Function LoadData(SheetName As String, CompareColumn As String) As Variant
Dim LastRowSheet As Long
Dim LoadCount As Long
Dim MarkedColor As Integer
Dim MyRange As String
Dim SheetData() As LineData

LastRowSheet = CountRows(SheetName)
ReDim SheetData(LastRowSheet) As LineData

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

LoadData = SheetData

End Function

Any idea on what I'm doing wrong?

 

[combo]

You can use early binding instead:

Public Function LoadData(SheetName As String, CompareColumn As String) As LineData()

combo

 

[Sorwen]

Thanks, combo that got it.

 

[Sorwen]

I've got the same problem again, but in a new way. An array wasn't working for me so now I'm trying a collection. and get the same error.

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

When I try to add the data to the collection "SheetCollection.Add (SheetData)" I get the error. 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?

 

[combo]

The proper syntax is:

SheetCollection.Add Item:=SheetData

and for objects:

Set LoadCollection = SheetCollection

combo

 

[Sorwen]

Thanks. Though it doesn't matter on the SheetCollection.Add Item:=SheetData. It was the same error when I did it that way as well. I'll try the Set on LoadCollection.

 

[Sorwen]

Errors with Argument not optional when I try adding Set.