sort function - from ascending to descending

[jimmyp]

hi

i have this bubblesort sub -

Sub BubbleSort(List() As Integer)

    Dim First As Integer, Last As Integer
    Dim i As Integer, j As Integer
    Dim Temp As Integer
    
    First = LBound(List)
    Last = UBound(List)
    For i = First To Last - 1
        For j = i + 1 To Last
            If List(i) > List(j) Then
                Temp = List(j)
                List(j) = List(i)
                List(i) = Temp
            End If
        Next j
    Next i
End Sub

but at the moment it sorts all the elements in ascending order, and i need it biggest element first ie. descending...

any idea how i change it, i guess it would be simple but i suppose it may need completely rewriting

thanks


jimmyp

[malik641]

You just had to change one sign:

Option Explicit
 
Sub BubbleSort(List() As Integer)
 
    Dim First As Integer, Last As Integer
    Dim i As Integer, j As Integer
    Dim Temp As Integer
    
    First = LBound(List)
    Last = UBound(List)
    For i = First To Last
        For j = i + 1 To Last
            If List(i) < List(j) Then 'This changed, was ">"
                Temp = List(j)
                List(j) = List(i)
                List(i) = Temp
            End If
        Next j
    Next i
    
    For i = First To Last
    Debug.Print List(i)
    Next
End Sub
 
Sub GiveList()
Dim List(0 To 4) As Integer
 
List(0) = 5
List(1) = 7
List(2) = 1
List(3) = 11
List(4) = 8
 
Call BubbleSort(List())
 
End Sub

[DKenny]

Here's a sort functionn that allows you to sort either Ascending or Descending. It can also sort a 2D array, based on a selected column.

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Comments:     This function sorts an array based on a column
'               number, in either ascending or descending order.
'
' Arguments:    OldArray    The array to be sorted
'               ColNum      An optional column number for 2d
'                           arrays.
'               ASC         An optional boolean value to sort
'                           in ASCending or descending order.
'
' Date          Developer       Action
' --------------------------------------------------------------
' 09/04/05      Declan Kenny    Initial version
'
Function SortArray(ByRef OldArray As Variant, Optional ByVal ColNum As Integer = 1, Optional ByVal ASC As Boolean = True)
 
Dim Sorted As Boolean
Dim ArrayRec As Integer
Dim temp As Variant
Dim ColCount As Integer
 
    Sorted = False
    Do While Not Sorted
        Sorted = True
        If ASC Then
            For ArrayRec = 1 To UBound(OldArray, ColNum) - 1
                If OldArray(ArrayRec, ColNum) > OldArray(ArrayRec + 1, ColNum) Then
                    For ColCount = 1 To UBound(OldArray, 2)
                        temp = OldArray(ArrayRec + 1, ColCount)
                        OldArray(ArrayRec + 1, ColCount) = OldArray(ArrayRec, ColCount)
                        OldArray(ArrayRec, ColCount) = temp
                    Next ColCount
                    Sorted = False
                End If
            Next ArrayRec
        Else
             For ArrayRec = 2 To UBound(OldArray, ColNum)
                If OldArray(ArrayRec, ColNum) > OldArray(ArrayRec - 1, ColNum) Then
                    For ColCount = 1 To UBound(OldArray, 2)
                        temp = OldArray(ArrayRec - 1, ColCount)
                        OldArray(ArrayRec - 1, ColCount) = OldArray(ArrayRec, ColCount)
                        OldArray(ArrayRec, ColCount) = temp
                    Next ColCount
                    Sorted = False
                End If
            Next ArrayRec
        End If
    Loop
End Function

[Webtest]

Nice work Declan ... even if it is old. However, I tried to use it but found that it is SLOW, SLOW, SLOW !!!

On my machine, sorting 2 columns - the sort column with 10 digit numbers, and one with about 50 characters of text - the function execution time is tightly fit by:

Time (sec) = 0.000002 * NumRows

A few thousand rows and you will be waiting for completion. I have over 28,000 rows in my data set ... it is MUCH faster just to copy the 2 columns into a clean sheet and use the Excel WorksheetFunction sort to do the work in less than a second (even with keeping ScreenUpdating ON!)

This is NOT a criticism ... just a hint to add to the bottom of this thread for anybody who discovers it in the future. As always, thanks Declan for your great support on this forum.