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.