Sorting Array In VBA
This page describes procedures for sorting arrays of values in VBA.
The VBA language has no support for sorting the values stored in an array. One method that can be used to sort
arrays is to put the data on to a worksheet, sort the data on the worksheet, and then read back the values from
the worksheet into the array. The other method for sorting arrays is to use the QSort algorithm to sort the array
in place. This page describes both methods, with variations on the QSort method.
This section describes code that uses Excel's range sorting method to sort the values in an array. The code first
creates a new worksheet so that this code won't clash with existing data on a worksheet. Then, it loads the values
in the array to a range on the new worksheet, begining in cell A1. That range is sorted
and the data is read back into the array in VBA. The code for SortViaWorksheet is shown below.
Sub SortViaWorksheet()
Dim Arr(1 To 5) As String
Dim WS As Worksheet
Dim R As Range
Dim N As Long
Arr(1) = "aaa"
Arr(2) = "zzz"
Arr(3) = "mmm"
Arr(4) = "ttt"
Arr(5) = "bbb"
Application.ScreenUpdating = False
Set WS = ThisWorkbook.Worksheets.Add
Set R = WS.Range("A1").Resize(UBound(Arr) - LBound(Arr) + 1, 1)
R = Application.Transpose(Arr)
R.Sort key1:=R, order1:=xlAscending, MatchCase:=False
For N = 1 To R.Rows.Count
Arr(N) = R(N, 1)
Next N
Application.DisplayAlerts = False
WS.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
For N = LBound(Arr) To UBound(Arr)
Debug.Print Arr(N)
Next N
End Sub
The SortViaWorksheet function works if you are using Excel and the structure of the workbook is
not protected. It the workbook is protected, you'll get an error when creating the new sheet, so you will have to have a
scratch sheet in place beforehand or use an unused region of an existing (and unprotected) worksheet. Due to these limitations,
coupled with the fact that VBA is used in many applications other than Excel, it may be desirable to employ a
VBA-only method that doesn't rely on any outside objects.
Sorting by any method is an expensive operations, especially with large arrays, due to the number of swaps made during the
sorting process. Before sorting a large array, it might be useful to test whether the array is already in sorted order and
thus does not need to be sorted. Procedures for testing if an array is sorted can be found on the IsArraySorted
page.
The code below in a implementation of the QSort algorithm for sorting an array. It will work with array that
contain either numeric or string values. The input array is sorted in place. That means that after the procedure
has ended, the original array will have been modified and sorted. The QSortInPlace and related
procedures will sort an array of numeric or string values in either ascending or descending order. The declaration
for QSortInPlace is shown below:
Public Function QSortInPlace( _
ByRef InputArray As Variant, _
Optional ByVal LB As Long = -1&, _
Optional ByVal UB As Long = -1&, _
Optional ByVal Descending As Boolean = False, _
Optional ByVal CompareMode As VbCompareMethod = vbTextCompare, _
Optional ByVal NoAlerts As Boolean = False) As Boolean
InputArray is the array to be sorted. LB is the first element of
the input array to sort. A value of -1 indicates to start sorting with the first element. UB
is the last element of the input array to sort. A value of -1 indicates to sort to the end of the array. By modifying
the values of LB and UB, you can sort only a subset of the array.
Descending, if False or omitted, causes the sort to progress in ascending order. If
Descending is True, the array is sorted in descending order. CompareMode
indicates whether the sorting is case sensitive or case insensitive. NoAlerts if True, supresses
error messages that may occur. The function returns True if the sort was successful or False if an error occurred.
There are several procedures that support the QSortInPlace function, so you should import the
entire module into your project.
 |
This page last updated: 7-June-2009. |