Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling Bubble Sort Function
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling Bubble Sort Function
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 --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling Bubble Sort Function
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 wrote in message om... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling Bubble Sort Function
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 --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling Bubble Sort Function
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 --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling Bubble Sort Function
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 -- Regards, Tom Ogilvy wrote in message om... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling Bubble Sort Function
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 --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 'bubble' chart where colors are used instead of bubble size | Charts and Charting in Excel | |||
How can I change the color of negative bubble in bubble chart | Charts and Charting in Excel | |||
How can I change the color of negative bubble in bubble chart | Charts and Charting in Excel | |||
ho to change in the bubble chart the bubble position and size | Charts and Charting in Excel | |||
How do I move a hidden bubble to the front in a bubble chart in E. | Excel Discussion (Misc queries) |