Sorting

Introduction#

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 https://stackoverflow.com/questions/152319/vba-array-sort-function

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
     Wend

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

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

  Wend

  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:

• https://stackoverflow.com/questions/28616373/copy-a-range-to-a-virtual-range

• https://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.Clear .SortFields.Add rng, xlSortOnValues, xlAscending, xlSortNormal .SetRange rng .Header = xlNo .Apply End With

  ’ Copy the results back to the array CopyRangeToArray rng, arr

  ’ Clear the objects Set rng = Nothing wbk.Close False xl.Quit

  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