CODEWITHSUNDEEP
excelvba
Philipp
Philipp

Reputation: 1

How to sort an array in Excel-VBA?

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

Upvotes: 0

Views: 9939

Answers (4)

David
David

Reputation: 1

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

Upvotes: 0

Fredrik
Fredrik

Reputation: 670

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.

Upvotes: 4

chris neilsen
chris neilsen

Reputation: 53166

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

Upvotes: 0

SNicolaou
SNicolaou

Reputation: 550

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

Upvotes: 0

Related Questions