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