Array Sort in VBA

Question

I have an array of size 182.123, and I want to sort them by a specific attribute of the array class. The class is called CFlujo, and the property I want to sort is a string called id_flujo. So far I have been making a similar bubble, but it takes too much time:

Sub sort_arreglo(arreglo As Variant) For x = LBound(arreglo) To UBound(arreglo) For y = x To UBound(arreglo) Dim aux As CFlujo aux = New CFlujo If UCase(arreglo(y).id_flujo) < UCase(arreglo(x).id_flujo) Then Set aux = arreglo(y) Set arreglo(y) = arreglo(x) Set arreglo(x) = aux End If Next y Next x End Sub 

So far I have been exploring Selection Sort , but I know that you cannot delete elements from the array, so I cannot make two lists to sort the values ​​from one to the other. I could put my data in a collection, but I had problems with the quality of the data if I did not allocate memory in advance (as in an array).

Answer 1

There are several things you can do to improve your runtime:

• Load all properties into an array
• Sort some pointers instead of objects
• Use a better algorithm like QucikSort

Example:

 Sub Sort(arreglo As Variant) Dim cache, vals(), ptrs() As Long, i As Long ReDim vals(LBound(arreglo) To UBound(arreglo)) ReDim ptrs(LBound(arreglo) To UBound(arreglo)) ' load the properties and fill the pointers For i = LBound(arreglo) To UBound(arreglo) vals(i) = UCase(arreglo(i).id_flujo) ptrs(i) = i Next ' sort the pointers QuickSort vals, ptrs, 0, UBound(vals) ' make a copy cache = arreglo ' set the value for each pointer For i = LBound(arreglo) To UBound(arreglo) Set arreglo(i) = cache(ptrs(i)) Next End Sub Private Sub QuickSort(vals(), ptrs() As Long, ByVal i1 As Long, ByVal i2 As Long) Dim lo As Long, hi As Long, p As Long, tmp As Long lo = i1 hi = i2 p = ptrs((i1 + i2) \ 2) Do While vals(ptrs(lo)) < vals(p): lo = lo + 1: Wend While vals(ptrs(hi)) > vals(p): hi = hi - 1: Wend If lo <= hi Then tmp = ptrs(hi) ptrs(hi) = ptrs(lo) ptrs(lo) = tmp lo = lo + 1 hi = hi - 1 End If Loop While lo <= hi If i1 < hi Then QuickSort vals, ptrs, i1, hi If lo < i2 Then QuickSort vals, ptrs, lo, i2 End Sub