# 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```

## 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```

Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist