Sorting

Sorting Algorithms

#back



The Bubble Sort

Bubble sort, sometimes incorrectly referred to as sinking sort, is a simple sorting algorithm that works by repeatedly stepping through the list to be sorted, comparing each pair of adjacent items and swapping them if they are in the wrong order. The pass through the list is repeated until no swaps are needed, which indicates that the list is sorted. The algorithm gets its name from the way smaller elements "bubble" to the top of the list. The Bubble sort is a slow sorting method when used on larger data sets and much slower than a Comb sort - read more about the Bubble sort on Wikipedia

The Functions will sort both numeric and text data with the numerical data sorting before text data. It will correctly sort "1,1,1,11,111" etc. It will correctly sort Dates ie. "01/12/2013, 12/10/2018, 01/09/2012". I have given you 1D Array and 2D Worksheet Array Functions together with examples of how to use the Functions including dynamic and hard-coded Ranges:

' force explicit variable declaration
Option Explicit

' Case-Insensitive - based on order in the ASCII table
' Performs a textual string comparison (A = a)
Option Compare Text

' Used at module level to declare the default lower bound for array subscripts
Option Base 1


' ## 1D Array Bubble sort
Sub BubbleSortExample()

    ' array
    Dim v As Variant
    v = BubbleSort(Array("Rabbit", "Cat", "Dog"), True)

End Sub

' ## 2D Worksheet array Bubble sort
Sub BubbleSort2DExample()

    ' dynamic Range, sort from "A1" to the first blank Cell
    Range(Range("A1"), Range("A1").End(xlDown)) = _
    BubbleSort2D((Range(Range("A1"), Range("A1").End(xlDown))), False)

    ' dynamic Range, sort from "A1" to the last Blank Row
    Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row) = _
    BubbleSort2D((Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)), False)

    ' hard-coded Range
    Range("A1:A16") = BubbleSort2D((Range("A1:A16")), True)
    [A1:A16] = BubbleSort2D((Range("A1:A16")), True)

End Sub

' ## BubbleSort, array bubble sort.  returns a sorted list according to the sort direction
Private Function BubbleSort(ByVal List As Variant, ByVal Ascending As Boolean) As Variant
    Dim i#, j#, v
    For i = 1 To UBound(List)
        For j = i + 1 To UBound(List)
            ' exclusive logical disjunction of two Boolean values
            If List(j) > List(i) Xor Ascending Then
                v = List(j)
                List(j) = List(i)
                List(i) = v
            End If
        Next j
    Next i
    BubbleSort = List
End Function

' ## BubbleSort2D, 2 dimensional array bubble sort.  returns a sorted list according to the sort direction
Private Function BubbleSort2D(ByVal List As Variant, ByVal Ascending As Boolean) As Variant
    Dim i#, j#, v
    For i = 1 To UBound(List)
        For j = i + 1 To UBound(List)
            ' exclusive logical disjunction of two Boolean values
            If List(j, 1) > List(i, 1) Xor Ascending Then
                v = List(j, 1)
                List(j, 1) = List(i, 1)
                List(i, 1) = v
            End If
        Next j
    Next i
    BubbleSort2D = List
End Function



The Comb Sort

Comb sort is a relatively simplistic sorting algorithm. It improves on bubble sort and rivals algorithms like Quicksort. The basic idea is to eliminate turtles, or small values near the end of the list, since in a bubble sort these slow the sorting down tremendously. Rabbits, large values around the beginning of the list, do not pose a problem in bubble sort. In bubble sort, when any two elements are compared, they always have a gap (distance from each other) of 1. The basic idea of comb sort is that the gap can be much more than 1. The pattern of repeated sorting passes with decreasing gaps is similar to Shellsort. Comb sort's passes do not completely sort the elements - read more about the Comb sort on Wikipedia

The Functions will sort both numeric and text data with the numerical data sorting before text data. It will correctly sort "1,1,1,11,111" etc. It will correctly sort Dates ie. "01/12/2013, 12/10/2018, 01/09/2012". I have given you 1D Array and 2D Worksheet Array Functions together with examples of how to use the Functions including dynamic and hard-coded Ranges:

' force explicit variable declaration
Option Explicit

' Case-Insensitive - based on order in the ASCII table
' Performs a textual string comparison (A = a)
Option Compare Text

' Used at module level to declare the default lower bound for array subscripts
Option Base 1


' ## 1D Array Comb sort
Sub CombSortExample()

    ' array
    Dim v As Variant
    v = CombSort(Array("Rabbit", "Cat", "Dog"), True)

End Sub

' ## 2D Worksheet array Comb sort
Sub CombSort2DExample()

    ' dynamic Range, sort from "A1" to the first blank Cell
    Range(Range("A1"), Range("A1").End(xlDown)) = _
    CombSort2D((Range(Range("A1"), Range("A1").End(xlDown))), True)

    ' dynamic Range, sort from "A1" to the last Blank Row
    Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row) = _
    CombSort2D((Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)), True)

    ' hard-coded the Range
    Range("A1:A16") = CombSort2D((Range("A1:A16")), True)
    [A1:A16] = CombSort2D((Range("A1:A16")), True)

End Sub

' ## CombSort, array comb sort.  returns a sorted list according to the sort direction
Private Function CombSort(ByVal List As Variant, ByVal Ascending As Boolean)
    Dim i#, j, gap#
    Dim swapped As Boolean
    gap = UBound(List) - 1
    Do While gap > 1 Or swapped
        If gap > 1 Then gap = (10 * gap) \ 13
        If gap = 9 Or gap = 10 Then gap = 11
        swapped = False
        For i = 1 To UBound(List) - gap
            If Ascending And List(i) > List(i + gap) Then
                j = List(i)
                List(i) = List(i + gap)
                List(i + gap) = j
                swapped = True
            ElseIf Not Ascending And List(i) < List(i + gap) Then
                j = List(i)
                List(i) = List(i + gap)
                List(i + gap) = j
                swapped = True
            End If
        Next i
    Loop
    CombSort = List
End Function

' ## CombSort2D, 2 dimensional array comb sort.  returns a sorted list according to the sort direction
Private Function CombSort2D(ByVal List As Variant, ByVal Ascending As Boolean)
    Dim i#, j, gap#
    Dim swapped As Boolean
    gap = UBound(List) - 1
    Do While gap > 1 Or swapped
        If gap > 1 Then gap = (10 * gap) \ 13
        If gap = 9 Or gap = 10 Then gap = 11
        swapped = False
        For i = 1 To UBound(List) - gap
            If Ascending And List(i, 1) > List(i + gap, 1) Then
                j = List(i, 1)
                List(i, 1) = List(i + gap, 1)
                List(i + gap, 1) = j
                swapped = True
            ElseIf Not Ascending And List(i, 1) < List(i + gap, 1) Then
                j = List(i, 1)
                List(i, 1) = List(i + gap, 1)
                List(i + gap, 1) = j
                swapped = True
            End If
        Next i
    Loop
    CombSort2D = List
End Function