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
– Tim Williams, Commented Oct 5, 2019 at 23:08
According to this article you could use VBA array list but you must have .Net Framework 3.5 installed. — Storax
– Storax, Commented Oct 6, 2019 at 5:48

Fredrik · Accepted Answer · 2021-05-06 19:08:41Z

4

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.

edited May 6, 2021 at 19:08

answered May 5, 2021 at 21:52

Fredrik

6721 gold badge7 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Fredrik Over a year ago

@chrisneilsen Thanks for pointing this out. It actually works if I set by_col to True rather than False! False is default.

Jon vB Feb 27 at 14:39

1. You can assign the result to your original array to save some memory. 2. I had a multi-dimensional array. The second argument is the column index you want to sort by. For my purpose - False worked --- False sorted the rows. True sorted the columns, which was not what I wanted. So, I did arr1 = Application.WorksheetFunction.Sort(arr1 6, , False)

SNicolaou · Accepted Answer · 2019-10-06 11:40:28Z

0

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

answered Oct 6, 2019 at 11:40

SNicolaou

5501 gold badge5 silver badges15 bronze badges

Comments

chris neilsen · Accepted Answer · 2020-02-03 04:51:39Z

0

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")

answered Feb 3, 2020 at 4:51

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

Comments

David · Accepted Answer · 2024-07-02 12:38:40Z

0

I know this question has been asked a long while ago, but I will answer it anyway, because I think that new people will keep coming across this question.

Try this:

Dim i, temp As Integer

i = 0

Do
    If (arrayName(i) > arrayName(i + 1)) Then
        temp = arrayName(i)
        arrayName(i) = arrayName(i + 1)
        arrayName(i + 1) = temp
        i = 0
    Else
        i = i + 1
    End If
Loop Until (i = UBound(arrayName))

answered Jul 2, 2024 at 12:38

David

1

1 Comment

Joost Mar 17 at 11:05

Do you know you declare i as a variant with /Dim i, temp As Integer/? You need to dim them both as Integer.

Collectives™ on Stack Overflow

How to sort an array in Excel-VBA?

4 Answers 4

2 Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related