Access Excel VBA sort Array items

This Access Excel VBA tutorial explains how to sort Array items in VBA in ascending order / descending order.

You may also want to read:

Use VBA Excel Function to sort data in ascending order

Access Excel VBA sort Array items

In an Array, you may have stored items that you want to sort. For example, if you store a name list, you may want to sort them in alphabetical order (Apple, Banana, Cat…).  If you store a number list, you may want to sort them in ascending order (100, 200, 400, 700…). Since there is no built in VBA function to sort Array items, we have to write a sorting logic manually to do the job. In this post, I write a Procedure that uses Bubble Sort method to do the sorting.

After sorting the Array items, you may want to know the ranking (Index) of specific Array items. Read my previous post to learn more.

Sort Array items in ascending order

The sorting sequence is:  blank > symbol > number > alphabet (disregard capital letter or not)

VBA Code

Public Sub sortAscending(arr as variant)
  Dim arr As Variant
  arr = Array(30, 70, 50, 99)
  lngMin = LBound(arr)
  lngMax = UBound(arr)
  For i = lngMin To lngMax - 1
    For j = i + 1 To lngMax
      If arr(i) > arr(j) Then
        strTemp = arr(i)
        arr(i) = arr(j)
        arr(j) = strTemp
      End If
    Next j
  Next i
End Sub

Example

Public Sub test()
   Dim arr As Variant
   arr = Array(30, 70, 50, 99)
   Call sortAscending(arr)
   MsgBox (arr(0) & " " & arr(1) & " " & arr(2) & " " & arr(3))
End Sub

Public Sub sortAscending(arr As Variant)
   lngMin = LBound(arr)
   lngMax = UBound(arr)
   For i = lngMin To lngMax - 1
     For j = i + 1 To lngMax
       If arr(i) > arr(j) Then
         strTemp = arr(i)
         arr(i) = arr(j)
         arr(j) = strTemp
       End If
     Next j
   Next i
End Sub

Result

 

Sort Array items in descending order

Simply replace  arr(i) > arr(j) with  arr(i) < arr(j) to sort Array in descending order.

Public Sub sortDescending(arr As Variant)
  lngMin = LBound(arr)
  lngMax = UBound(arr)
  For i = lngMin To lngMax - 1
    For j = i + 1 To lngMax
      If arr(i) < arr(j) Then
        strTemp = arr(i)
        arr(i) = arr(j)
        arr(j) = strTemp
      End If
    Next j
  Next i
End Sub