# Sorting

Unlike the .NET framework, the Visual Basic for Applications library does not include routines to sort arrays.

There are two types of workarounds: 1) implementing a sorting algorithm from scratch, or 2) using sorting routines in other commonly-available libraries.

# Algorithm Implementation - Quick Sort on a One-Dimensional Array

From VBA array sort function? (opens new window)

Public Sub QuickSort(vArray As Variant, inLow As Long, inHi As Long)

  Dim pivot   As Variant
  Dim tmpSwap As Variant
  Dim tmpLow  As Long
  Dim tmpHi   As Long

  tmpLow = inLow
  tmpHi = inHi

  pivot = vArray((inLow + inHi) \ 2)

  While (tmpLow <= tmpHi)

     While (vArray(tmpLow) < pivot And tmpLow < inHi)
        tmpLow = tmpLow + 1

     While (pivot < vArray(tmpHi) And tmpHi > inLow)
        tmpHi = tmpHi - 1

     If (tmpLow <= tmpHi) Then
        tmpSwap = vArray(tmpLow)
        vArray(tmpLow) = vArray(tmpHi)
        vArray(tmpHi) = tmpSwap
        tmpLow = tmpLow + 1
        tmpHi = tmpHi - 1
     End If


  If (inLow < tmpHi) Then QuickSort vArray, inLow, tmpHi
  If (tmpLow < inHi) Then QuickSort vArray, tmpLow, inHi

End Sub

# Using the Excel Library to Sort a One-Dimensional Array

This code takes advantage of the Sort class in the Microsoft Excel Object Library.

For further reading, see:

  • [Copy a range to a virtual range](http://stackoverflow.com/questions/28616373/copy-a-range-to-a-virtual-range)
  • [How to copy selected range into given array?](http://stackoverflow.com/questions/18000617/how-to-copy-selected-range-into-given-array)
  • Sub testExcelSort()
    Dim arr As Variant
    InitArray arr
    ExcelSort arr
    End Sub
    Private Sub InitArray(arr As Variant)
    Const size = 10
    ReDim arr(size)
    Dim i As Integer
    ' Add descending numbers to the array to start
    For i = 0 To size
        arr(i) = size - i
    Next i
    End Sub
    Private Sub ExcelSort(arr As Variant)
    ' Ininitialize the Excel objects (required)
    Dim xl As New Excel.Application
    Dim wbk As Workbook
    Set wbk = xl.Workbooks.Add
    Dim sht As Worksheet
    Set sht = wbk.ActiveSheet
    ' Copy the array to the Range object
    Dim rng As Range
    Set rng = sht.Range("A1")
    Set rng = rng.Resize(UBound(arr, 1), 1)
    rng.Value = xl.WorksheetFunction.Transpose(arr)
    ' Run the worksheet's sort routine on the Range
    Dim MySort As Sort
    Set MySort = sht.Sort
    With MySort
        .SortFields.Add rng, xlSortOnValues, xlAscending, xlSortNormal
        .SetRange rng
        .Header = xlNo
    End With
    ' Copy the results back to the array
    CopyRangeToArray rng, arr
    ' Clear the objects
    Set rng = Nothing
    wbk.Close False
    End Sub
    Private Sub CopyRangeToArray(rng As Range, arr)
    Dim i As Long
    Dim c As Range
    ' Can't just set the array to Range.value (adds a dimension)
    For Each c In rng.Cells
        arr(i) = c.Value
        i = i + 1
    Next c
    End Sub