Visual Basic for Applications/Bubble Sort One Dimensional Arrays

Summary

This page deals with the sorting of single dimensioned arrays. These are typically the ones used for placing lists into variants with the Array method. It is more rare to find such a sorting routine in VBA, since most sorting is done in two dimensions.

Bubble Sorting One Dimensional Arrays

• The procedure BubbleSort1DArray() uses the slowest of sorting methods. However, there would seem to be no disadvantage in doing so here, since few lists ever attain great size. There are options for ascending or descending sorts, and for the method of return. If a return array is provided, that will be used, otherwise the input array will be changed by returning in that.

The Code Module

Function BubbleSort1DArray(vIn As Variant, bAscending As Boolean, Optional vRet As Variant) As Boolean
    ' Sorts the single dimension list array, ascending or descending
    ' Returns sorted list in vRet if supplied, otherwise in vIn modified
        
    Dim First As Long, Last As Long
    Dim i As Long, j As Long, bWasMissing As Boolean
    Dim Temp As Variant, vW As Variant
    
    First = LBound(vIn)
    Last = UBound(vIn)
    
    ReDim vW(First To Last, 1)
    vW = vIn
    
    If bAscending = True Then
        For i = First To Last - 1
            For j = i + 1 To Last
                If vW(i) > vW(j) Then
                Temp = vW(j)
                vW(j) = vW(i)
                vW(i) = Temp
                End If
            Next j
        Next i
    Else 'descending sort
        For i = First To Last - 1
            For j = i + 1 To Last
                If vW(i) < vW(j) Then
                Temp = vW(j)
                vW(j) = vW(i)
                vW(i) = Temp
                End If
            Next j
        Next i
    End If
  
   'find whether optional vRet was initially missing
    bWasMissing = IsMissing(vRet)
   
   'transfers
   If bWasMissing Then
     vIn = vW  'return in input array
   Else
     ReDim vRet(First To Last, 1)
     vRet = vW 'return with input unchanged
   End If
   
   BubbleSort1DArray = True

End Function