How to sort an array in Excel-VBA?

Question

I want to sort an array of values declared as double in descending order.

The commands array.Sort and array.Reverse don't work.

Please note that I want to sort the array with an embedded VBA function and not by a sorting algorithm written by myself.

Sub sortiereMesswerte()
  Dim werte(15) As Double
  Dim i As Integer

  Sheets("Eingabe").Select

  'initliaze array
  For i = 0 To 15
    werte(i) = Cells(i + 2, 2)
  Next i

  'Sort Array
  werte.Sort
  werte.Reverse

  Sheets("Ausgabe").Select

  'print array
  For i = 0 To 15
    Cells(i + 2, 2) = werte(i)
  Next i
End Sub

Looks like you're trying to use VB.NET methods which do not exist in VBA - mkae sure you're reading the correct docs for the language you're using (it's not always obvious when you just see snippets but the MS docs are pretty clear on which one you're reading about...) — Tim Williams, Oct 05 '19 at 23:08
According to this [article](https://excelmacromastery.com/vba-arraylist/#Sorting) you could use VBA array list but you must have [.Net Framework 3.5](https://excelmacromastery.com/vba-arraylist/#VBA_ArrayList_Automation_Error) installed. — Storax, Oct 06 '19 at 05:48

Fredrik · Answer 1 · 2021-05-06T19:08:41.943

The new Sort function seems to work. I tried the following code:

Sub testsort()
    Dim arr1, arr2
    Dim index
    
    ReDim arr1(1 To 10)
    
    For index = 1 To 10
        arr1(index) = Chr(65 + Int(Rnd() * 25))
    Next index
    
    Debug.Print "arr1:  " + Join(arr1, "-")
    arr2 = Application.WorksheetFunction.Sort(arr1, , , True)
    Debug.Print "sort:  " + Join(arr2, "-")
    
End Sub

Output of one run looks like this:

arr1:  L-I-K-G-B-G-Y-B-J-J
sort:  B-B-G-G-I-J-J-K-L-Y

Note that I had to supply the third argument (by_col = True), to make it work. It works well for numbers as well.

I don't know how it performs, but it's good enough for me and might help someone else who's frustrated that this wasn't solved long ago.

@chrisneilsen Thanks for pointing this out. It actually works if I set by_col to True rather than False! False is default. — Fredrik, May 06 '21 at 08:14

score 0 · Answer 2 · answered Oct 06 '19 at 11:40

as per @Storax comment above, you can use the ArrayList object but with the limitations of the .net framework version which can be an issue if you are sharing the workbook with others . Then you can write the code as below:

Sub sortiereMesswerte()
  Dim werte As Object
  Dim i As Integer

  Set werte = CreateObject("System.Collections.ArrayList")
  Sheets("Eingabe").Select

  'initliaze array
  For i = 0 To 15
    werte.Add Cells(i + 2, 2).Value
  Next i

  'Sort Array
  werte.Sort
  werte.Reverse

  Sheets("Ausgabe").Select

  'print array
  For i = 0 To 15
    Cells(i + 2, 2) = werte(i)
  Next i

End Sub

score 0 · Answer 3 · answered Feb 03 '20 at 04:51

If you have a version of Excel that included the new Dynamic Arrays, you can use the new SORT function

Sub Demo()
    Dim rng As Range
    Dim v

    ' Get Data reference
    Set r = Worksheets("Eingabe").Range("B2:B17")

    ' Sort data
    v = Application.WorksheetFunction.Sort(r.Value)

    'Return to a sheet
    Worksheets("Ausgabe").Range("B2:B17") = v
End Sub

That said, you could also do this directly on the sheet. In sheet Ausgabe cell B2 put

=SORT(Eingabe!"B2:B17")

How to sort an array in Excel-VBA?

3 Answers3