Example
This code takes advantage of the Sort class in the Microsoft Excel Object Library.
For further reading, see:
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
