In VBA gibt es keine Möglichkeit, ein Array mit­tels ein­er inte­gri­erten Funk­tion zu sortieren. Darum wird die Überzahl der Pro­gram­mier­er auf eine der bei­den bekan­ntesten Sortier­meth­o­d­en zurück­greifen: Bub­ble­Sort und Quick­Sort. Obwohl Bub­ble­Sort etwas leichter les­bar ist, kann ich die Meth­ode nicht empfehlen; sie ist nur bis zu etwa 25 Ein­trä­gen in Sachen Geschwindigkeit dem Quick­Sort eben­bür­tig oder über­legen.

In der hier vorgestell­ten Rou­tine wird ein leeres Blatt mit 65.000 Zufall­szahlen gefüllt und diese wer­den in der Nach­barspalte sortiert zurück­gegeben. Den Code find­en Sie in gepack­ter Form in dieser Datei als reine Text­datei und zum direk­ten Import in Tabelle1 als *.cls.

Prinzip­iell kön­nen Sie den Code so ver­wen­den, wie er ist. Kleine Anpas­sun­gen sind naturgemäß erforder­lich, wenn ein ander­er Bere­ich ange­sprochen wer­den soll. Darum ist auch einiges an Vari­ablen mehr vorhan­den, als bei ein­er fes­ten Vor­gabe oder wenn der Code für erfahrene Pro­gram­mier­er gedacht wäre.

Das ober­ste Mod­ul SortierTest() dient auss­chließlich dazu, dass Sie ein wenig die Geschwindigkeit ein­schätzen kön­nen. Die Begren­zung auf 65.500 Daten­sätze ist wegen älter­er Excel-Ver­sio­nen (bis Excel 2003) erforder­lich, ab der 2007^er-Ver­sion kön­nen Sie auch gerne 1 Mil­lio­nen Zahlen erzeu­gen lassen. 🙂  Einige Hin­weise zu diesen Zeilen:

lRow = 65500 + fRow – 1

wird Sie vielle­icht etwas nach­den­klich machen. So erre­iche ich, dass exakt 65.500 Zeilen erzeugt wer­den. Die erste Zeile ist ja Zeile 2. Die 65.000 dazu addiert würde ja 65.002 als let­zte Zeile des Bere­ichs ergeben. Darum muss noch 1 sub­trahiert wer­den.

Dass dort anschließend per Funk­tion ZUFALLSBEREICH() erst eine Zufall­szahl in A2 gener­iert wird, haben Sie gewiss erkan­nt. Per Aus­füll­funk­tion wer­den nun alle Zellen des Bere­ichs mit ein­er weit­eren Zufall­szahl beschrieben. Anschließend wer­den die Funk­tion­swerte in feste Werte umge­wan­delt, damit sich beim Sortieren nichts mehr durch Neu­berech­nung ändern kann.

Der so gefüllte Bere­ich wird nun in ein Array ein­ge­le­sen. Dabei ist wichtig, dass solch ein durch Kopie von 1 Spalte des Arbeits­blatts gefülltes Array zwar einspaltig zu sein scheint aber auf jeden Fall wie ein mehrspaltiges Array ange­sprochen wer­den muss: aArray(1,1) spricht das erste Ele­ment an. Und falls Sie jet­zt meinen, dass das im Nor­mal­fall doch das zweite Ele­ment sein müsse, weil ja Arrays (wenn kein Option Base 1 ver­wen­det wurde) typ­is­cher­weise mit dem Ele­ment 0 begin­nen, dann sehen Sie hier eine weit­ere Aus­nahme. Durch direk­tes Ein­le­sen von der Tabelle erstellte Arrays sind immer 1‑basiert.

Bleibt noch der Aufruf der Sorti­er-Rou­tine. Die Sub QSort, welche den Quick­sort-Algo­rith­mus enthält, braucht für den Aufruf die „Zeilen­num­mer” der ersten und der let­zten Zeile (Ele­ments) des Arrays. Prinzip­iell ließe sich das auch inner­halb der Rou­tine berech­nen, aber da diverse rekur­sive Aufrufe erfol­gen, wäre der Rechenaufwand extrem hoch und auch unnütz, weil es sich in einem definierten Array ja um fest­ste­hende Werte han­delt. – Um nicht bei jedem exter­nen Aufruf der Sub erneut die Über­gabepa­ra­me­ter zu schreiben (ich bin näm­lich schreib­faul), ist eine kleine Proze­dur zwis­chengeschal­tet, welche die erste und die let­zte Zeile des Arrays berech­net und diese bei­den Werte als Argu­ment an die eigentliche Sorti­er-Proze­dur übergibt:

Sub QuickSortStart(aData)
    Call QSort(aData, LBound(aData), UBound(aData))
End Sub

Sub QSort(aData, a As Long, e As Long)
   Dim x As Long, y As Long, vntTmp As Variant, vntPivot As Variant
   x = a
   y = e
   vntPivot = aData((a + e) \ 2)
   Do While x <= y
      Do While aData(x) < vntPivot
         x = x + 1
      Loop
      Do While aData(y) > vntPivot
         y = y - 1
      Loop
      If x <= y Then
         vntTmp = aData(x)
         aData(x) = aData(y)
         aData(y) = vntTmp
         x = x + 1
         y = y - 1
      End If
   Loop
   If a < y Then Call QSort(aData, a, y)
   If x < e Then Call QSort(aData, x, e)
End Sub

Hin­weis: Das Ganze funk­tion­iert auss­chließlich mit eindi­men­sion­alen Arrays. Dabei ist es keineswegs uner­he­blich, wie diese gefüllt wor­den sind. Wenn Sie beispiel­sweise einen solchen Code ver­wen­den, um das Array zu füllen:

Dim aData(lRow), Ze As Long
For Ze = 2 To lRow
   aData(Ze - 1) = Cells(Ze, Sp)
Next Ze

dann ist das Array (nor­maler­weise) nicht nur null­basiert, es ist auch „echt” einspaltig. Dadurch sprechen Sie das erste Ele­ment so an:

Wert = aData(0)

Und vor allen Din­gen: Der Teil

'So eingelesene Arrays müssen mehrdimensional angesprochen werden
'Darum Transfer in ein 1-dimensionales Array
ReDim aData(UBound(MyData)) 
For i = 1 To UBound(MyData) 
   aData(i) = MyData(i, 1)
Next i

aus dem SortierTest() ist natür­lich über­flüs­sig; es liegt ja schon ein eindi­men­sion­ales Array vor und muss nicht erst aus dem mehrdi­men­sion­alen kon­vertiert wer­den. – Natür­lich kön­nen Sie auch ein leeres Tabel­len­blatt erstellen oder ver­wen­den, dort die zu sortieren­den Dat­en hinein kopieren, sortieren und dann und das Array zurückschreiben. Diese Lösung ist vielfach für mehrspaltige Arrays/Bereiche empfehlenswert.

▲ nach oben …

Rück­mel­dun­gen / Feed­back gerne per Mail an mich (G.Mumme@Excel-ist-sexy.de)

Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?

Dann würde ich mich über einen Beitrag Ihrer­seits z.B. 1,00 € freuen … (← Klick mich!)