tiehaze
asked on
Quicksort for multiple columns in excel vba userform
I have a multicolumn listbox that I want to have the option of sorting any of the 7 columns. I would use the bubblesort, but there can be potentially 35000 in the list which makes it kind of slow. I found the code below for quicksort, but I am not sure how I can modify it so that I can sort multiple columns, not just one. Can anyone help me?
Sub Quicksort (List() As Long, min As Integer, max As Integer)
Dim med_value As Long
Dim hi As Integer
Dim lo As Integer
Dim i As Integer
' If the list has no more than 1 element, it's sorted.
If min >= max Then Exit Sub
' Pick a dividing item.
i = Int((max - min + 1) * Rnd + min)
med_value = List(i)
' Swap it to the front so we can find it easily.
List(i) = List(min)
' Move the items smaller than this into the left
' half of the list. Move the others into the right.
lo = min
hi = max
Do
' Look down from hi for a value < med_value.
Do While List(hi) >= med_value
hi = hi - 1
If hi <= lo Then Exit Do
Loop
If hi <= lo Then
List(lo) = med_value
Exit Do
End If
' Swap the lo and hi values.
List(lo) = List(hi)
' Look up from lo for a value >= med_value.
lo = lo + 1
Do While List(lo) < med_value
lo = lo + 1
If lo >= hi Then Exit Do
Loop
If lo >= hi Then
lo = hi
List(hi) = med_value
Exit Do
End If
' Swap the lo and hi values.
List(hi) = List(lo)
Loop
' Sort the two sublists
Quicksort List(), min, lo - 1
Quicksort List(), lo + 1, max
End Sub
Sub Quicksort (List() As Long, min As Integer, max As Integer)
Dim med_value As Long
Dim hi As Integer
Dim lo As Integer
Dim i As Integer
' If the list has no more than 1 element, it's sorted.
If min >= max Then Exit Sub
' Pick a dividing item.
i = Int((max - min + 1) * Rnd + min)
med_value = List(i)
' Swap it to the front so we can find it easily.
List(i) = List(min)
' Move the items smaller than this into the left
' half of the list. Move the others into the right.
lo = min
hi = max
Do
' Look down from hi for a value < med_value.
Do While List(hi) >= med_value
hi = hi - 1
If hi <= lo Then Exit Do
Loop
If hi <= lo Then
List(lo) = med_value
Exit Do
End If
' Swap the lo and hi values.
List(lo) = List(hi)
' Look up from lo for a value >= med_value.
lo = lo + 1
Do While List(lo) < med_value
lo = lo + 1
If lo >= hi Then Exit Do
Loop
If lo >= hi Then
lo = hi
List(hi) = med_value
Exit Do
End If
' Swap the lo and hi values.
List(hi) = List(lo)
Loop
' Sort the two sublists
Quicksort List(), min, lo - 1
Quicksort List(), lo + 1, max
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I tried to set it up like this:
Private Sub CommandButton3Sort_Click(l
but it keeps on giving me the error:
Compile error:
Procedure declaration does not match description of event or procedure having the same name
Any ideas what to do?
Private Sub CommandButton3Sort_Click(l
but it keeps on giving me the error:
Compile error:
Procedure declaration does not match description of event or procedure having the same name
Any ideas what to do?
Run the QuickSort function (passing ListBox as a first parameter) from the Button3 click event handler...
Sub Button3_Click()
Quicksort ListBox1, .....
End Sub
Sub Button3_Click()
Quicksort ListBox1, .....
End Sub
ASKER
What is .....?
ASKER
And do I put the Sub Quicksort in a module?
ASKER
Private Sub CommandButton3_Click()
Quicksort ListBox3
End Sub
When I click on CommandButton3 and I debug it, it has Quicksort highlighted and says argument not optional
Quicksort ListBox3
End Sub
When I click on CommandButton3 and I debug it, it has Quicksort highlighted and says argument not optional
With .... I meant the other parameters :))
The first parameter is listbox, the second one is the zero-based index of the column you would like to sort, the third one is 0, and fourth is number of items in the listbox minus one
for example (sorting the second column):
Quicksort ListBox3, 1, 0, ListBox3.ListCount - 1
The first parameter is listbox, the second one is the zero-based index of the column you would like to sort, the third one is 0, and fourth is number of items in the listbox minus one
for example (sorting the second column):
Quicksort ListBox3, 1, 0, ListBox3.ListCount - 1
ASKER
K, so within Userform1, I have the following code that is not working... can you please tell me what I am doing wrong?
Private Sub CommandButton3Sort_Click()
Quicksort ListBox3, 1, 0, ListBox3.ListCount - 1
End Sub
Sub Quicksort(lb As Object, column As Integer, min As Integer, max As Integer)
Dim med_value As Long, lb As Object, column As Integer, min As Integer, max As Integer
Dim hi As Integer
Dim lo As Integer
Dim i As Integer
' If the list has no more than 1 element, it's sorted.
If min >= max Then Exit Sub
' Pick a dividing item.
i = Int((max - min + 1) * Rnd + min)
med_value = lb.List(i, column)
' Swap it to the front so we can find it easily.
lb.List(i, column) = lb.List(min, column)
' Move the items smaller than this into the left
' half of the list. Move the others into the right.
lo = min
hi = max
Do
' Look down from hi for a value < med_value.
Do While lb.List(hi, column) >= med_value
hi = hi - 1
If hi <= lo Then Exit Do
Loop
If hi <= lo Then
lb.List(lo, column) = med_value
Exit Do
End If
' Swap the lo and hi values.
lb.List(lo, column) = lb.List(hi, column)
' Look up from lo for a value >= med_value.
lo = lo + 1
Do While lb.List(lo, column) < med_value
lo = lo + 1
If lo >= hi Then Exit Do
Loop
If lo >= hi Then
lo = hi
lb.List(hi, column) = med_value
Exit Do
End If
' Swap the lo and hi values.
lb.List(hi, column) = lb.List(lo, column)
Loop
' Sort the two sublists
Quicksort lb, min, lo, column - 1
Quicksort lb, lo, column + 1, max
End Sub
Private Sub CommandButton3Sort_Click()
Quicksort ListBox3, 1, 0, ListBox3.ListCount - 1
End Sub
Sub Quicksort(lb As Object, column As Integer, min As Integer, max As Integer)
Dim med_value As Long, lb As Object, column As Integer, min As Integer, max As Integer
Dim hi As Integer
Dim lo As Integer
Dim i As Integer
' If the list has no more than 1 element, it's sorted.
If min >= max Then Exit Sub
' Pick a dividing item.
i = Int((max - min + 1) * Rnd + min)
med_value = lb.List(i, column)
' Swap it to the front so we can find it easily.
lb.List(i, column) = lb.List(min, column)
' Move the items smaller than this into the left
' half of the list. Move the others into the right.
lo = min
hi = max
Do
' Look down from hi for a value < med_value.
Do While lb.List(hi, column) >= med_value
hi = hi - 1
If hi <= lo Then Exit Do
Loop
If hi <= lo Then
lb.List(lo, column) = med_value
Exit Do
End If
' Swap the lo and hi values.
lb.List(lo, column) = lb.List(hi, column)
' Look up from lo for a value >= med_value.
lo = lo + 1
Do While lb.List(lo, column) < med_value
lo = lo + 1
If lo >= hi Then Exit Do
Loop
If lo >= hi Then
lo = hi
lb.List(hi, column) = med_value
Exit Do
End If
' Swap the lo and hi values.
lb.List(hi, column) = lb.List(lo, column)
Loop
' Sort the two sublists
Quicksort lb, min, lo, column - 1
Quicksort lb, lo, column + 1, max
End Sub
What error are you getting?
ASKER
Run-time error '-2147024809 (8007057)':
Could not get the List property. Invalid argument
When I debug it, it highlights:
med_value = lb.List(i, column)
I also deleted:
lb As Object, column As Integer, min As Integer, max As Integer
from:
Dim med_value As Long, lb As Object, column As Integer, min As Integer, max As Integer
because it said I had duplicates
Could not get the List property. Invalid argument
When I debug it, it highlights:
med_value = lb.List(i, column)
I also deleted:
lb As Object, column As Integer, min As Integer, max As Integer
from:
Dim med_value As Long, lb As Object, column As Integer, min As Integer, max As Integer
because it said I had duplicates
What are values of "i" and "column"? How many columns and rows do you have in your listbox?
ASKER
i = 54 and column = 70?
There are only 7 columns in the listbox.
I am kind of a beginner with using vba, so forgive me if I am not seing something simple.
There are only 7 columns in the listbox.
I am kind of a beginner with using vba, so forgive me if I am not seing something simple.
Change this:
' Sort the two sublists
Quicksort lb, min, lo, column - 1
Quicksort lb, lo, column + 1, max
into this:
' Sort the two sublists
Quicksort lb, column, min, lo - 1
Quicksort lb, column, lo + 1, max
' Sort the two sublists
Quicksort lb, min, lo, column - 1
Quicksort lb, lo, column + 1, max
into this:
' Sort the two sublists
Quicksort lb, column, min, lo - 1
Quicksort lb, column, lo + 1, max
ASKER
Allright, I think I am getting somewhere. Now the problem that I am getting is that it is only sorting the second column no matter what I select in the following dropdown combobox:
Dim arr As Variant
arr = Array("Asset Code", "Fund Number", "Cusip", "Name", "Shares", "Market Value", "Cost")
ComboBox1.List = arr
ComboBox1.ListIndex = 0
How do I get it to sort, dependent upon what is selected?
Dim arr As Variant
arr = Array("Asset Code", "Fund Number", "Cusip", "Name", "Shares", "Market Value", "Cost")
ComboBox1.List = arr
ComboBox1.ListIndex = 0
How do I get it to sort, dependent upon what is selected?
Try this:
Quicksort ListBox3, ComboBox1.ListIndex, 0, ListBox3.ListCount - 1
Quicksort ListBox3, ComboBox1.ListIndex, 0, ListBox3.ListCount - 1
ASKER
Two questions... first, when it sorts, it is only sorting that column. I need each row to stay intact. For example if I had the following:
Jon 10
Ty 5
Ben 2
and I sorted it by the second column, it is looking like:
Jon 2
Ty 5
Ben 10
when it should be:
Ben 2
Ty 5
Jon 10
My second questions is about sorting text. When i try to sort a column with text, it is giving me the error:
run type error 13:
Type mismatch
When I debug it, the following line is highlighted:
med_value = lb.List(i, column)
Jon 10
Ty 5
Ben 2
and I sorted it by the second column, it is looking like:
Jon 2
Ty 5
Ben 10
when it should be:
Ben 2
Ty 5
Jon 10
My second questions is about sorting text. When i try to sort a column with text, it is giving me the error:
run type error 13:
Type mismatch
When I debug it, the following line is highlighted:
med_value = lb.List(i, column)
That is more complicated... I will look into it...
About the second question - you cannot sort text with this algorithm as it supports only numbers. It would have to be modified to support strings also.
About the second question - you cannot sort text with this algorithm as it supports only numbers. It would have to be modified to support strings also.
ASKER
Thanks for all of your help.
ASKER