Sorting methods

 2000-11-06    Sorting    0    51

With the procedure below it is easy to sort any valid Excel cell range. You can also use this procedure to work around the limit of 3 sorting columns in the built-in sorting feature. If you want to sort by multiple columns, repeat the sorting procedure for each column, starting with the least significant one.

Sub SortCellRange(objSortRange As Range, Optional lngSortColumn As Long = 1, _
    Optional lngHeader As Long = xlYes, Optional lngSortOrder As Long = xlAscending, _
    Optional blnMatchCase As Boolean = True)
' uses the built in worksheet sorting, very fast
' valid input for lngHeader: xlYes, xlNo, xlGuess
' valid input for lngSortOrder: xlAscending, xlDescending
' example: SortCellRange Range("A1:D1000"), 1
' you might also want to set Application.Calculation = xlCalculationManual before sorting
' remember to restore the calculation after sorting, e.g. to Application.Calculation = xlCalculationManual before sorting
    If objSortRange Is Nothing Then Exit Sub
    With objSortRange
        If .Areas.Count = 1 Then ' does not support multiple ranges
            If lngSortColumn >= 1 And lngSortColumn < = .Columns.Count Then
                On Error Resume Next ' ignore errors, e.g. when worksheet is protected
                .Sort Key1:=.Cells(1, lngSortColumn), Order1:=lngSortOrder, _
                    Header:=lngHeader, MatchCase:=blnMatchCase, Orientation:=xlSortColumns
                On Error GoTo 0
            End If
        End If
    End With
End Sub
The procedure below can be used to sort a single dimension array variable in ascending or descending order. It is very fast on small arrays, but can be slow on very large arrays.

Sub BubbleSort(varArray As Variant, Optional blnSortAscending As Boolean = True)
' sorts a single dimension array variable in ascending or descending order
' fast on small arrays, slow on very large arrays
' example: BubbleSort MyArrayVariable
Dim i As Long, j As Long, varTemp As Variant, blnSwap As Boolean
    If Not IsArray(varArray) Then Exit Sub

    For i = LBound(varArray) To UBound(varArray) - 1
        For j = i + 1 To UBound(varArray)
            If blnSortAscending Then
                blnSwap = varArray(i) > varArray(j)
            Else
                blnSwap = varArray(i) < varArray(j)
            End If
            If blnSwap Then
                varTemp = varArray(i)
                varArray(i) = varArray(j)
                varArray(j) = varTemp
            End If
        Next j
    Next i
End Sub
The procedure below can be used to sort a single dimension array variable in ascending order. It is very fast on large arrays with values in random order, but can be slow on arrays with sorted or almost sorted values.

Sub QuickSort(ByRef varArray As Variant, Optional ByVal lngFirst As Long = -1, Optional ByVal lngLast As Long = -1)
' sorts a single dimension array variable in ascending order
' very fast sorting on random data (n Log n comparisons)
' slow on almost sorted data (uses much stack space), avoid use on sorted arrays
Dim lngLow As Long, lngHigh As Long, lngMiddle As Long, varMiddleItem As Variant, varItem As Variant
    If lngFirst = -1 Then lngFirst = LBound(varArray) ' initial lower bound
    If lngLast = -1 Then lngLast = UBound(varArray) ' initial upper bound

    If lngFirst < lngLast Then ' more than 1 item in the array
        lngMiddle = (lngFirst + lngLast) \ 2 ' the middle array item
        varMiddleItem = varArray(lngMiddle)
        lngLow = lngFirst
        lngHigh = lngLast
        Do
            ' find first item >= middle array item
            Do While varArray(lngLow) < varMiddleItem
                lngLow = lngLow + 1
            Loop
            ' find first item <= middle array item
            Do While varArray(lngHigh) > varMiddleItem
                lngHigh = lngHigh - 1
            Loop
            If lngLow <= lngHigh Then ' swap the items
                varItem = varArray(lngLow)
                varArray(lngLow) = varArray(lngHigh)
                varArray(lngHigh) = varItem
                lngLow = lngLow + 1
                lngHigh = lngHigh - 1
            End If
        Loop While (lngLow <= lngHigh)
        If lngFirst < lngHigh Then
            ' sort the lower half of the array
            QuickSort varArray, lngFirst, lngHigh
        End If
        If lngLow < lngLast Then
            ' sort the upper half of the array
            QuickSort varArray, lngLow, lngLast
        End If
    End If
End Sub
The procedure below can be used to sort a single dimension array variable in ascending order. It can only be used on arrays containing integer or long values, but it is very fast.

Sub CountSort(varArray As Variant, ByVal lngValueMin As Long, ByVal lngValueMax As Long)
' sorts a single dimension array variable in ascending order
' varArray must contain Integer or Long values only, very fast sorting
' lngValueMin must be less than or equal to the minimum value in varArray
' lngValueMax must be larger than or equal to the maximum value in varArray
Dim arrCount() As Long, i As Long, j As Long, lngIndex As Long
    If Not IsArray(varArray) Then Exit Sub

    ReDim arrCount(lngValueMin To lngValueMax) ' all items are initiated to 0
    ' count the values in varArray
    For i = LBound(varArray) To UBound(varArray)
        arrCount(varArray(i)) = arrCount(varArray(i)) + 1
    Next i
    ' write the items back into varArray
    lngIndex = LBound(varArray)
    For i = lngValueMin To lngValueMax
        For j = 1 To arrCount(i)
            varArray(lngIndex) = i
            lngIndex = lngIndex + 1
        Next j
    Next i
    Erase arrCount
End Sub

This workbook example compares 7 different sorting methods and shows how long time each use to sort random values or text so you can determine which one is the best for your project.
Click here to download this file.
Updated: 2009-09-05 Requires: XL97 File size: 73 kB




Leave a comment:

Your comment will only be published after it has been moderated and found spam free.
Your e-mail address will only be used to display your Gravatar.