Re: Save & Sort

Thanks Charlie !

"Charlie" <Charlie@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:5238246C-8FEB-4DE3-AFD7-CE0D1C0F266C@xxxxxxxxxxxxxxxx
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!
>

.