RE: Save & Sort

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

By the way, this sort function is Case Sensitive. You will need to add the
UCase statements, or try using Option Compare Text.

"Charlie" wrote:

Bubble sorts, as you have discovered, are simple to code but excruciatingly
slow when sorting large arrays. You can copy your array to a scratch
work*** and use the Excel Sort function on it, or here is a very fast
"Heap" sort.

This function I adapted from a Fortran Heap sort function I wrote circa
1983. It has ugly "GoTo" statements but I tested it to be faster than those
versions that were later rewritten to remove the GoTo's.

My version uses 1-based string Arrays. I modified it to use 0-based
Variants for you but have not tested it. Google "Heap Sort" and "Quick Sort"
for other sorting algorithms.

Have Fun,
Charlie

Public Function SortList(List() As Variant) As Variant
'
' fast "Heap" sort alogrithm from Knuth - The Art of Computer Programming
'
Dim i As Long
Dim j As Long
Dim nent As Long
Dim ist As Long
Dim lst As Long
Dim tmp As String
Dim buf() As String
'
' set sort pointers to the midpoint and endpoint of the array (NOTE - use
the
' integer division operator!)
'
nent = UBound(List)
ist = nent \ 2 + 1
lst = nent
buf = List
'
' do an ascending sort
'
110:
If ist > 0 Then
ist = ist - 1
tmp = buf(ist)
Else
tmp = buf(lst)
buf(lst) = buf(1)
lst = lst - 1
If lst = 1 Then
buf(lst) = tmp
SortList = buf
Exit Function
End If
End If
'
j = ist
'
120:
i = j
j = j * 2
'
If j = lst Then
If tmp >= buf(j) Then
buf(i) = tmp
GoTo 110
End If
buf(i) = buf(j)
GoTo 120
End If
'
If j > lst Then
buf(i) = tmp
GoTo 110
End If
'
If buf(j) < buf(j + 1) Then j = j + 1
If tmp >= buf(j) Then
buf(i) = tmp
GoTo 110
End If
'
buf(i) = buf(j)
GoTo 120
'
End Function

"Redan" wrote:

Hello,

I have a very big excel file (approx. 58 MB) and every time I open my
work*** I run a module which go thru each row (in 4 different columns ex.
| Ticker | Name | Category | Country ) and save the data in a dictionary and
once finished (which means approx after 8000 rows) I sort the result using
the following function :

Public Function SortArray(MyArray As Variant) As Variant
'Sort array
For lLoop = 0 To UBound(MyArray)
For lLoop2 = lLoop To UBound(MyArray)
If UCase(MyArray(lLoop2)) < UCase(MyArray(lLoop)) Then
str1 = MyArray(lLoop)
str2 = MyArray(lLoop2)
MyArray(lLoop) = str2
MyArray(lLoop2) = str1
End If
Next lLoop2
Next lLoop

'Output sorted array
SortArray = MyArray

End Function

My question is : Is there a way to save the result faster and also sort it
faster than the one I use at the moment ?

Thanks in advance.

Cheers!

.

Quantcast