Excel VBA Quickest way to sort an array of numbers in descending order?

I have used the Shell sort algorithm successfully. Runs in the blink of an eye when tested for N=10000 using an array generated with VBA Rnd() function - don't forget to use the Randomize statement for generating test arrays. It was easy to implement and short and efficient enough for the number of elements I was dealing with. Reference is given in the code comments.

' Shell sort algorithm for sorting a double from largest to smallest.
' Adopted from "Numerical Recipes in C" aka NRC 2nd Edition p330ff.
' Speed is on the range of N^1.25 to N^1.5 (somewhere between bubble and quicksort)
' Refer to the NRC reference for more details on efficiency.
' 
Private Sub ShellSortDescending(ByRef a() As Double, N As Integer)

    ' requires a(1..N)

    Debug.Assert LBound(a) = 1

    ' setup

    Dim i, j, inc As Integer
    Dim v As Double
    inc = 1

    ' determine the starting incriment

    Do
        inc = inc * 3
        inc = inc + 1
    Loop While inc <= N

    ' loop over the partial sorts

    Do
        inc = inc / 3

        ' Outer loop of straigh insertion

        For i = inc + 1 To N
            v = a(i)
            j = i

            ' Inner loop of straight insertion
            ' switch to a(j - inc) > v for ascending

            Do While a(j - inc) < v
                a(j) = a(j - inc)
                j = j - inc
                If j <= inc Then Exit Do
            Loop
            a(j) = v
        Next i
    Loop While inc > 1
End Sub

I answered this question myself a long time ago, meaning I had to come back to my very first VBA archived files. So I found this old code, which I took from a book. First it saves values (from selection intersected with a table column) to array ar(x) then sort them from smallest to biggest. To sort there are 2 bucles, the first one (Do Loop Until sw=0) and the second one (For x=1 To n Next) compares value a(x) with value a(x+1), keeping in a(x) the biggest number and in ar(x+1) the smallest number. The first bucle repeats until is sorted form smallest to biggest. I actually used this code to insert a rows above every selected cell in a budget column (TblPpto[Descripcion]). Hope it helps!

Sub Sorting()
Dim ar() As Integer, AX As Integer
Set rng = Intersect(Selection, Range("TblPpto[Descripcion]")) 'Cells selected in Table column
n = rng.Cells.Count 'Number of rows
ReDim ar(1 To n)
x = 1
For Each Cell In rng.Cells
    ar(x) = Cell.Row 'Save rows numbers to array ar()
    x = x + 1
Next
Do 'Sort array ar() values
    sw = 0  'Condition to finish bucle
    For x = 1 To n - 1
        If ar(x) > ar(x + 1) Then 'If ar(x) is bigger
            AX = ar(x)            'AX gets bigger number
            ar(x) = ar(x + 1)     'ar(x) changes to smaller number
            ar(x + 1) = AX        'ar(x+1) changes to bigger number
            sw = 1                'Not finished sorting
        End If
    Next
Loop Until sw = 0
'Insert rows in TblPpto
fila = Range("TblPpto[#Headers]").Row
For x = n To 1 Step -1
    [TblPpto].Rows(ar(x) - fila).EntireRow.Insert
Next x
End Sub

The trincot code simply expanded as a function. Have fun with that!

Function sort1DimArray(thatArray As Variant, descending As Boolean) As Variant
Dim arr As Object, i As Long, j As Long`

Set arr = CreateObject("System.Collections.ArrayList")

For i = LBound(thatArray) To UBound(thatArray)
    arr.Add thatArray(i)
Next i

arr.Sort

If descending = True Then
    arr.Reverse
End If
'shortens empty spaces
For i = 0 To (arr.count - 1)
    If Not IsEmpty(arr.Item(i)) Then
        thatArray(j) = arr.Item(i)
        j = j + 1
    End If
Next i

ReDim Preserve thatArray(0 To (j - 1))
sort1DimArray = thatArray

End Function