A G
asked on
Sorting an Array with over 2 million members - Analyzing on Excel VBA
I have a vba code which calculates over 2 million numbers and puts them into an array. I want to sort these values inside the array. As far as I can see there is no sort function in excel vba.
I tried Qsortinplace which can be found http://www.cpearson.com/excel/SortingArrays.aspx .
But it seems it doesnt work when there are 2 million members inside the array.
I guess something can be arranged when filling the array in the first place.
What is the best way to sort the huge arrays?
I tried Qsortinplace which can be found http://www.cpearson.com/excel/SortingArrays.aspx .
But it seems it doesnt work when there are 2 million members inside the array.
I guess something can be arranged when filling the array in the first place.
What is the best way to sort the huge arrays?
Actually a variant array may be larger (can't find my reference on that). Let me see if I can load a variant array with 3 million records using variant arrays and my quicksort algorithm...
Dave
Dave
Here's a QuickSort macro I use all the time, I only changed integer to variant. It SHOULD work, and right now I'm trying to figure out how to load an array with > 1MM records without waiting forever.
Give it and the heapsort a shot, as you're already in a position to test. Note usage on the Qsort...
Call QSort(myArray, LBound(myArray), UBound(myArray))
Will repaint the variant array myArray in sorted order. Its easy enough to add a boolean in the mix to determine ascending/descending and I can help with that if you like it. right now its ascending.
Let me know if this works for you:
Give it and the heapsort a shot, as you're already in a position to test. Note usage on the Qsort...
Call QSort(myArray, LBound(myArray), UBound(myArray))
Will repaint the variant array myArray in sorted order. Its easy enough to add a boolean in the mix to determine ascending/descending and I can help with that if you like it. right now its ascending.
Let me know if this works for you:
Sub QSort(sortArray As Variant, ByVal leftIndex As Integer, ByVal rightIndex As Integer)
Dim compValue As Variant
Dim i As Variant
Dim j As Variant
Dim tempVar As Variant
i = leftIndex
j = rightIndex
compValue = sortArray(Int((i + j) / 2))
Do
Do While (sortArray(i) < compValue And i < rightIndex)
i = i + 1
Loop
Do While (compValue < sortArray(j) And j > leftIndex)
j = j - 1
Loop
If i <= j Then
tempVar = sortArray(i)
sortArray(i) = sortArray(j)
sortArray(j) = tempVar
i = i + 1
j = j - 1
End If
Loop While i <= j
If leftIndex < j Then QSort sortArray, leftIndex, j
If i < rightIndex Then QSort sortArray, i, rightIndex
End Sub
My bad. I had one type-o in my integer-> variant conversion.
Sorry for the SPAM!
Here's a QuickSort macro I use all the time, I only changed integer to variant. It SHOULD work, and right now I'm trying to figure out how to load an array with > 1MM records without waiting forever.
Give it and the heapsort a shot, as you're already in a position to test. Note usage on the Qsort...
Call QSort(myArray, LBound(myArray), UBound(myArray))
Will repaint the variant array myArray in sorted order. Its easy enough to add a boolean in the mix to determine ascending/descending and I can help with that if you like it. right now its ascending.
Let me know if this works for you:
Sorry for the SPAM!
Here's a QuickSort macro I use all the time, I only changed integer to variant. It SHOULD work, and right now I'm trying to figure out how to load an array with > 1MM records without waiting forever.
Give it and the heapsort a shot, as you're already in a position to test. Note usage on the Qsort...
Call QSort(myArray, LBound(myArray), UBound(myArray))
Will repaint the variant array myArray in sorted order. Its easy enough to add a boolean in the mix to determine ascending/descending and I can help with that if you like it. right now its ascending.
Let me know if this works for you:
Sub QSort(sortArray As Variant, ByVal leftIndex As variant, ByVal rightIndex As variant)
Dim compValue As Variant
Dim i As Variant
Dim j As Variant
Dim tempVar As Variant
i = leftIndex
j = rightIndex
compValue = sortArray(Int((i + j) / 2))
Do
Do While (sortArray(i) < compValue And i < rightIndex)
i = i + 1
Loop
Do While (compValue < sortArray(j) And j > leftIndex)
j = j - 1
Loop
If i <= j Then
tempVar = sortArray(i)
sortArray(i) = sortArray(j)
sortArray(j) = tempVar
i = i + 1
j = j - 1
End If
Loop While i <= j
If leftIndex < j Then QSort sortArray, leftIndex, j
If i < rightIndex Then QSort sortArray, i, rightIndex
End Sub
I think that the thing to do is to not sort 2,000,000 records but rather to create the 'array' sorted as you build it. I have 'array' in single quotes because what I suggest is that you use the VBA Dictionary object instead. It is like a collection but faster. Here is a short tutorial.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Here's a heapsort algorithm using long (I personally use QuickSort, but wanted something documented with long array indexes, so here it is - untested by me):
http://www.source-code.biz/snippets/vbasic/1.htm
If not, then it can be done with a collection and collection sort. I can assist with this, but first await your response to the first question, above.
Cheers,
Dave