Sorting UDT Arrays

[ragkag]

I'm pretty familiar with VB6 but am somewhat lost when trying to sort arrays. I have a UDT array and would like to sort it by multiple elements, being able to specify ascending or descending for each element. I have searched here for examples but have not been able to understand most of them. Any help would be greatly appreciated.
Thank You
Bob Gregory

[SamOscarBrown]

Look at this thread: https://www.vbforums.com/showthread....Sort-UDT-array

[ragkag]

Look at this thread: https://www.vbforums.com/showthread....Sort-UDT-array

I saw that. The first example (sorting one element) made sense to me but the second(multiple elements) I did not understand.

Thanks for the tip though.

[Elroy]

I've sorted UDTs on many occasions. To me, they sort the same way as anything else we wish to sort, just create a "temp" variable for swapping, and do your sort. If I'm not overly worried about speed, I just always do For i = LBound(?) To UBound(?) - 1: For j = i + 1 To UBound(?) type sorts.

If I wanted to sort on multiple UDT elements, I'd probably just concatenate them before I did my sort test, somehow accommodating for the different length of various Strings being concatenated. I suppose you could do two (nested) IF tests rather than concatenation, depending on what the data were.

Some quickly written pseudo-code:

Select Case True
Case UDT(i).SortItem1 < UDT(j).SortItem1 ' Nothing to do.
Case UDT(i).SortItem1 > UDT(j).SortItem1 ' Swap them.
Case UDT(i).SortItem1 = UDT(j).SortItem1
    If UDT(i).SortItem2 > UDT(j).SortItem2 ' Swap them.
    Else ' Nothing to do.
End Select

[The trick]

https://www.vbforums.com/showthread....=1#post4942769

[dilettante]

You can also run away from QBasic era UDT arrays and move to something like a fabricated ADO Recordset:

Option Explicit

Private Sub Form_Load()
    Dim rsFabbed As ADODB.Recordset
    Dim FieldIndices As Variant
    Dim State As Long
    Dim City As Long
    Dim Street As Long

    'Create a fabricated Recordset and generate some dummy sample data:
    Set rsFabbed = New ADODB.Recordset
    With rsFabbed
        .CursorLocation = adUseClient
        With .Fields
            .Append "Street", adVarWChar, 255
            .Append "City", adVarWChar, 127
            .Append "State", adVarWChar, 2
            .Append "Occupants", adSmallInt
        End With
        .Open
        .Sort = "[State] DESC,[City] ASC,[Street] ASC"
        FieldIndices = Array(0, 1, 2, 3)
        For State = 1 To 12
            For City = 1 To 7
                For Street = 1 To 12
                    .AddNew FieldIndices, Array(CStr(Street) & " " & MonthName(Street), _
                                                WeekdayName(City), _
                                                UCase$(Left$(MonthName(State), 2)), _
                                                (Street + City + State) Mod 10 + 1)
                Next
            Next
        Next
        .UpdateBatch
    End With

    'Display the data:
    With MSHFlexGrid1
        .FixedCols = 0
        Set .DataSource = rsFabbed
    End With
    rsFabbed.Close
End Sub

Private Sub Form_Resize()
    If WindowState <> vbMinimized Then
        MSHFlexGrid1.Move 0, 0, ScaleWidth, ScaleHeight
    End If
End Sub

[baka]

the sorting from The trick is "advanced" and a really good one. I would go with that.

as for me I always use quicksort base function that I "update" with whatever UDT/type I have.
example:

Private Sub QuickSort(c() As bpicData, ByVal First As Long, ByVal Last As Long)
    Dim Low As Long, High As Long
    Dim MidValue As Long
    Static Tmp As bpicData

    Low = First
    High = Last
    MidValue = c((First + Last) \ 2).Sort
        
    Do
        While c(Low).Sort < MidValue
            Low = Low + 1
        Wend
            
        While c(High).Sort > MidValue
            High = High - 1
        Wend
            
        If Low <= High Then
            Tmp = c(Low): c(Low) = c(High): c(High) = Tmp
            Low = Low + 1
            High = High - 1
        End If
    Loop While Low <= High
        
    If First < High Then QuickSort c, First, High
    If Low < Last Then QuickSort c, Low, Last
End Sub

what I do when I need a new one is to change c() as bpicdata and tmp as bpicdata with the UDT I will use
and MidValue with the type of variable if numerical or string.
in this case I use .Sort that is used for the sorting and its a LONG variable (MidValue and .Sort are always same type)

another thing that I could do is to create a "sort" variable (like this case here)
what I mean is:
if the sorting is complex, like it should follow some criteria that some items need to be prioritized even if the value is lower than the other item,
instead of comparing multiple variables with each other I create a ".sort" variable that is created when an item is added.
so the .sort value could be:

1xxx612 for an item with value 612 but priority 1
2xxx374 for an item with value 374 but priority 2

that way the sorting is very fast and simple when needed. no need to add any conditions when I call quicksort.
(sure the cons is that your UDT has this .sort variable, but with todays computers it should not be a problem with memory, as long the UDT is dynamically created)

[ragkag]

Thanks to everyone. I will check out the examples and hope I can figure them out

[Elroy]

Dilettante mentioned disconnected ADO recordsets, and that's a good idea. Here's a CodeBank link where I've attempted to simplify these.

Basically, you'd just "define" your recordset, and then use something like the following to sort it:

rs.Sort="SortField1, SortField2, SortField3, ..."

Easier and faster than what you'd probably do with a UDT. It's just a bit more work to set it up, but again, I've tried to simplify that part.

If you don't need these UDTs for API calls, then a disconnected recordset is probably a better alternative, especially if you'll be focusing a lot on sorting.

[dilettante]

Yeah, it depends on what you are trying to do.

[The trick]

You could also use qsort c-function if install this Add-in.