Issue with user defined type

[jerH]

I have defined a type as

Public Type entryRecord
    firstName As String
    lastName As String
    amount As String
    letterDate As String
End Type

I then create an array of these entries, and scan a data worksheet to populate the array.

The idea is to then sort the array by lastName, and then populate a listbox with entries that are in alphabetical order by last name.

So in my userform_initialize i populate the array, but when I try to pass it to a sort routine, I get this error

Only user-defined types defined in public object modules can be coerced to or from a variant or passed to late-bound functions

here's a snippet of the code with the relevant lines...the last one is where the error occurs

Dim entryArray() As entryRecord
    Dim sortedArray() As entryRecord

    ReDim entryArray(1 To numEntries)   'size the array to the number of entries
    ReDim sortedArray(1 To numEntries)

    sortedArray = BubbleSrt(entryArray, True)

the bubble sort routine is one I found online and then modified to use the custom type

Public Function BubbleSrt(ArrayIn, Ascending As Boolean)


Dim SrtTemp As Variant
Dim i As Long
Dim j As Long




If Ascending = True Then
    For i = LBound(ArrayIn) To UBound(ArrayIn)
         For j = i + 1 To UBound(ArrayIn)
             If ArrayIn(i).lastName > ArrayIn(j).lastName Then
                 SrtTemp = ArrayIn(j)
                 ArrayIn(j) = ArrayIn(i)
                 ArrayIn(i) = SrtTemp
             End If
         Next j
     Next i
Else
    For i = LBound(ArrayIn) To UBound(ArrayIn)
         For j = i + 1 To UBound(ArrayIn)
             If ArrayIn(i).lastName < ArrayIn(j).lastName Then
                 SrtTemp = ArrayIn(j)
                 ArrayIn(j) = ArrayIn(i)
                 ArrayIn(i) = SrtTemp
             End If
         Next j
     Next i
End If


BubbleSrt = ArrayIn


End Function

This is a little outside my normal realm, so I'm not sure how to proceed....any help would be greatly appreciated.

 

[JLGWhiz]

Outside my expertise too, but the way I read the message, it is not finding this

Public Type entryRecord
    firstName As String
    lastName As String
    amount As String
    letterDate As String
End Type

in a public code module, which would be one of the numbered modules, not UserForm, Sheet or ThisWorkbook.

 

[jerH]

Nope, it is in a module....I'm thinking it has something to do with the bubble sort function not explicitly typing the array that is passed in. I'm going to mess around with that a bit...been reading on what "late binding" is.

 

[Domenic]

I think you'll have to declare ArrayIn as an array having the type entryRecord...

Public Function BubbleSrt(ArrayIn[COLOR=#ff0000]() As entryRecord[/COLOR], Ascending As Boolean)

 

[jerH]

That was it. I also had to define the internal variables (can't leave them variant) and provide a return type.

The sort function looks like this now:

Public Function BubbleSrt(ArrayIn() As entryRecord, Ascending As Boolean) As entryRecord()


Dim SrtTemp As entryRecord
Dim i As Long
Dim j As Long




If Ascending = True Then
    For i = LBound(ArrayIn) To UBound(ArrayIn)
         For j = i + 1 To UBound(ArrayIn)
             If ArrayIn(i).lastName > ArrayIn(j).lastName Then
                 SrtTemp = ArrayIn(j)
                 ArrayIn(j) = ArrayIn(i)
                 ArrayIn(i) = SrtTemp
             End If
         Next j
     Next i
Else
    For i = LBound(ArrayIn) To UBound(ArrayIn)
         For j = i + 1 To UBound(ArrayIn)
             If ArrayIn(i).lastName < ArrayIn(j).lastName Then
                 SrtTemp = ArrayIn(j)
                 ArrayIn(j) = ArrayIn(i)
                 ArrayIn(i) = SrtTemp
             End If
         Next j
     Next i
End If


BubbleSrt = ArrayIn


End Function

Now I'm on to trying to make sure the various entryStrings line up column-wise