API reference · Arrays

Sort

Stably sort a 1D array or the rows of a 2D array by one column.

FunctionArrays→VariantSource L130

Signature#

Function Sort(ByVal vArr As Variant, Optional ByVal lCol As Integer = -1, Optional ByVal iSortSens As Integer = 1) As Variant

Parameters#

Parameter	VBA type	Default	Description
`vArr`	`Variant`ByVal	Required	Allocated 1D or 2D array. A 1D input is temporarily expanded to one column and returned as 1D.
`lCol`	`Integer`ByVal	`-1`	Actual sorting-column subscript. Any negative value selects LBound(vArr, 2) after expansion. The parameter is Integer, so its range is narrower than a Long subscript.
`iSortSens`	`Integer`ByVal	`1`	1 sorts ascending; 0 sorts descending. Any other value returns the original values and order, retaining the input's 1D or 2D representation.

Returns#

Variant containing a sorted array with the same rank and bounds as the accepted input. Returns Empty for unsupported rank or a sorting column outside the accepted bounds.

Example#

SEE THE TRANSFORMATIONAmount ascending · column 2

Before

transactions2D · 3 × 2

Rows 1..3 · Columns 1..2

row / col	1	2
1	“Invoice A”	75
2	“Invoice B”	20
3	“Invoice C”	75

After

ordered2D · 3 × 2

Rows 1..3 · Columns 1..2

row / col	1	2
1	“Invoice B”	20
2	“Invoice A”	75
3	“Invoice C”	75

The shape stays the same. Invoice A remains before Invoice C because equal keys keep source order.

Shaded headers are array indices, not data. Quoted cells are text. Values shown are derived from the source, not an Excel execution.

Sub ExampleSort()
    Dim a As New Arrays
    Dim transactions As Variant, ordered As Variant

    ReDim transactions(1 To 3, 1 To 2)
    transactions(1, 1) = "Invoice A": transactions(1, 2) = 75
    transactions(2, 1) = "Invoice B": transactions(2, 2) = 20
    transactions(3, 1) = "Invoice C": transactions(3, 2) = 75

    ordered = a.Sort(transactions, 2, 1)
    Debug.Print ordered(1, 1) ' Expected: Invoice B
    Debug.Print ordered(2, 1) ' Expected: Invoice A
    Debug.Print ordered(3, 1) ' Expected: Invoice C; equal keys keep source order
End Sub

Behavior and edge cases#

A recursive MergeSort sorts Long row indices, then copies complete rows into an output array. Equal comparison keys keep their original relative order in either direction.
Row and column bounds are preserved. A 1D input retains its original element bounds.
The source uses native VBA > and < comparisons. It does not apply Filter's CDbl/UCase comparison rules or provide a custom ordering for mixed types, Null, error values or objects. Incompatible comparison values can raise runtime errors.
A negative lCol is always a request for the first column. A genuinely negative source column cannot be selected explicitly through this parameter unless it is the first column.
A valid sorting column is checked before the invalid-direction no-op branch. Non-arrays and unsupported ranks return Empty. The caller's input array is not rearranged.

Performance#

O(r log r + r × c) time for a 2D array: index merge sorting plus the output copy. Auxiliary index buffers use O(r), the recursion uses O(log r), and the result uses O(r × c). For 1D input, time is O(r log r) and result/index storage is O(r).

View implementation

Function Sort(ByVal vArr As Variant, Optional ByVal lCol As Integer = -1, Optional ByVal iSortSens As Integer = 1) As Variant
    'Sorts a one- or two-dimensional array by column using a stable merge sort

    Dim vArr_Out                As Variant
    Dim lIndex()                As Long
    Dim lTemp()                 As Long
    Dim iDim__                  As Integer
    Dim lRow                    As Long
    Dim lCol_Out                As Long

    iDim__ = Me.DimCounter(vArr)

    Select Case iDim__
        Case 1
            vArr = Me.DimExpand(vArr)
        Case 2
        Case Else
            Exit Function
    End Select

    If lCol < 0 Then lCol = LBound(vArr, 2)
    If lCol < LBound(vArr, 2) Or lCol > UBound(vArr, 2) Then Exit Function

    If iSortSens <> 0 And iSortSens <> 1 Then
        If iDim__ = 1 Then
            ReDim vArr_Out(LBound(vArr, 1) To UBound(vArr, 1))
            For lRow = LBound(vArr, 1) To UBound(vArr, 1)
                vArr_Out(lRow) = vArr(lRow, LBound(vArr, 2))
            Next lRow
            Sort = vArr_Out
        Else
            Sort = vArr
        End If
        Exit Function
    End If

    ReDim lIndex(LBound(vArr, 1) To UBound(vArr, 1))
    ReDim lTemp(LBound(vArr, 1) To UBound(vArr, 1))

    For lRow = LBound(vArr, 1) To UBound(vArr, 1)
        lIndex(lRow) = lRow
    Next lRow

    Call MergeSortRows(vArr, lIndex, lTemp, LBound(lIndex), UBound(lIndex), CLng(lCol), iSortSens)

    If iDim__ = 1 Then
        ReDim vArr_Out(LBound(vArr, 1) To UBound(vArr, 1))
        For lRow = LBound(vArr_Out) To UBound(vArr_Out)
            vArr_Out(lRow) = vArr(lIndex(lRow), LBound(vArr, 2))
        Next lRow
    Else
        ReDim vArr_Out(LBound(vArr, 1) To UBound(vArr, 1), LBound(vArr, 2) To UBound(vArr, 2))
        For lRow = LBound(vArr_Out, 1) To UBound(vArr_Out, 1)
            For lCol_Out = LBound(vArr_Out, 2) To UBound(vArr_Out, 2)
                vArr_Out(lRow, lCol_Out) = vArr(lIndex(lRow), lCol_Out)
            Next lCol_Out
        Next lRow
    End If

    Sort = vArr_Out
End Function