Calling Bubble Sort Function

[terryspencer2003]

I have an 1D array that I have filled within a For Next Loop. There
are roughly 60,000 values within the array. Upon filling the array, I
want to sort the data within the array. The array is called
CalculationArray. When I call the bubble sort function, it fails
within the function and gives a type mismatch error. (It fails on "For
i = 1 To List - 1").

Why is this so?


'Call Sort Function and Sort Bidding Array

BubbleSort CalculationArray

Function BubbleSort(List As Variant)
' Sorts an array using bubble sort algorithm
Dim First As Long, Last As Long
Dim i As Long, j As Long
Dim Temp As Long

First = LBound(List)
Last = UBound(List)
For i = 1 To List - 1 'THIS IS WHERE IT FAILS
For j = i + 1 To List
If List(i) > List(j) Then
Temp = List(j)
List(j) = List(i)
List(i) = Temp
End If
Next j
Next i

End Function

Thanks

TS

 

[pikus]

Bubble sort is too slow. This was written to sort an Excel spreadsheet
but you can definitely adapt it to work with your array. Basically it
searches the un-sorted items to find the lowest value and places that
at the top.

Sub SortByCount(ctrStart As Long, ctrEnd As Long)

Dim foo As Integer
Dim bar As Integer
Dim fizz As Integer
Dim nums As Worksheet

Set nums = Worksheets("FinalNumbers")
bar = ctrStart

Do
foo = 0
fizz = -100
For x = bar To ctrEnd
If nums.Cells(x, 7).Value >= fizz Then
foo = x
fizz = nums.Cells(x, 7).Value
End If
Next x

If foo <> bar And foo <> 0 Then
nums.Rows(foo).Cut
nums.Rows(bar).Insert
End If

bar = bar + 1
Loop Until bar = ctrEnd

End Sub

 

[Jim Cone]

Hello Terry,

I believe the error comes from using the wrong limits on the loop.
Replace "For i = 1 to List -1" with...
"For i = First to Last"

Regards,
Jim Cone
San Francisco, CA

 

[pikus]

Your For...Next Loop does not recognize "List" as a number. It is an
Array right? What you want to use in it's stead is the number of items
in the array. - Pikus

 

[pikus]

Also, unless you specifically changed this, the first item in the array
is numbered 0 (zero). So you want to do it like:

For x = 0 To listCount - 1

- Pikus

 

[Tom Ogilvy]

Some comments on the advice so far:
a bubble sort is one of the slowest sort algorithms
for the "i" loop you want to go to last - 1
You don't want to use the number of items in the array - you want to look at
the lower and upper bounds.
you can't assume list has a lower bound of 0


Function BubbleSort(List As Variant)
' Sorts an array using bubble sort algorithm
Dim First As Long, Last As Long
Dim i As Long, j As Long
Dim Temp As Long

First = LBound(List)
Last = UBound(List)
For i = First To Last - 1
For j = i + 1 To Last
If List(i) > List(j) Then
Temp = List(j)
List(j) = List(i)
List(i) = Temp
End If
Next j
Next i

End Function

 

[pikus]

This is something I wrote to sort rows of information in a spreadsheet.
If you adapt it to what you're doing it should be faster. Let me know
what you think. - Pikus

Sub RankByCount(ctrStart As Long, ctrEnd As Long)

Dim foo As Integer
Dim bar As Integer
Dim fizz As Integer
Dim nums As Worksheet

Set nums = Worksheets("FinalNumbers")
bar = ctrStart

Do
foo = 0
fizz = -100
For x = bar To ctrEnd
If nums.Cells(x, 7).Value >= fizz Then
foo = x
fizz = nums.Cells(x, 7).Value
End If
Next x

If foo <> bar And foo <> 0 Then
nums.Rows(foo).Cut
nums.Rows(bar).Insert
End If

bar = bar + 1
Loop Until bar = ctrEnd

End Sub