Hello,

I have a very big excel file (approx. 58 MB) and every time I open my
worksheet 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!

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
worksheet 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
worksheet 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!

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
worksheet 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
worksheet 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!

Thanks Charlie !

"Charlie" wrote in message
...
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
worksheet 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
worksheet 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!