Given a table -
Number Surname Province Country Gender DOB Salary
187 Fillion ON USA Male 01/10/1980 $144000
3 Simon GA USA Female 07/08/1972 $176000
How would i sort the entire table at the same time using Bubble sort function on VBA??
Given a table -
Number Surname Province Country Gender DOB Salary
187 Fillion ON USA Male 01/10/1980 $144000
3 Simon GA USA Female 07/08/1972 $176000
How would i sort the entire table at the same time using Bubble sort function on VBA??
Bubble Sorting is long complex method of sorting a list by comparing adjacent values ONE AT A TIME and swapping those two items only if they are in the wrong order, then starting the whole process again, over and over until you make it all the way through the data without doing any swaps at all, meaning they are finally in the correct order. It is a brute force sort, it should only be used when no other simpler option exists.
Based on your question, the simplest of all solutions DOES exist. You can simply highlight the entire table of data and apply instant sorting based on the column(s) of your choosing, such as sorting by DOB. Bubble sorting would not be a good candidate for something as simple as this.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
I know bubble sort is not ideal for this but my prof wants it done that way
the code provided is:
But something is missing as it doesn't take sort for the adjacent values.Please Login or Register to view this content.
Last edited by JBeaucaire; 03-28-2015 at 05:43 PM.
I've added the missing CODE /CODE tags above in your post. Be sure to include them in all your postings, as per forum rules.
Your professor, IMO, is doing you a disservice. Excel's builtin SORT tools IS the tool to use for sorting table data. Period. This exercise seems like makework, and almost pointless at that, as in the real world you would not do bubble sorting in VBA memory of an entire table, you'd simply call the sorting tool. Period. Time is money.
Worse, he has provided you the standard basic Bubble Sort code that DOES do what bubble-sorting does best, sort a single set of values in memory, not a table. To expand the capabilities of that simple macro to entail an entire table of data is not a trivial expansion to the code. If he feels it's an important lesson for you to be able to do this, he should provide the non-trivial expanded code and explain it to you.
I did a little Google searching on VBA matrix sorting and found a perfect example of what I mean. Compare all the code in this thread to what he's given you, you'll see the original technique he provided demonstrated in this larger code, but as I said, the added complexity is not trivial. Hopefully you will find that code helpful in this homework, but I stand by my assertion this is mostly wasting your valuable learning time on something you'd never do.
http://www.vbaexpress.com/kb/getarticle.php?kb_id=498
Although Jerry is correct in saying the Bubble Sort is a very inefficient method for sorting a list ("a brute force" method), I disagree when he says it is a COMPLEX method - its advantage is its simplicity, as it is a SIMPLE algorithm to implement. The study of the Bubble Sort introduces the student to many important concepts (comparing data elements, swapping elements, nested loops etc.) which are common to many other sorting methods. If you are studying sorting algorithms, it is important to appreciate how each of them work, so that you can choose the appropriate method to suit the real-life situations. The Bubble Sort is fine if you only have a few items to sort, and the Insertion Sort and Selection Sort both offer improvements on the basic Bubble Sort, but use many of the same elements. You might then move to the Shell-Metzner Sort and eventually the Quicksort and Treesort methods, which involve recursive programming, but you are not likely to be able to attempt those methods until you have tried the others (and there are many others). The acknowledged master of these algorithms was Knuth - I advise you to study his work.
Pete
About your problem with the other columns.
The way that I sort 2-Dimensional arrays is to create a one dimensional array of row numbers.
Then I sort that array based on the values of the key column (or more complicated comparison).
You have your unsorted array, List. and it looks like your key column is 1.
I would create ArrayOfRowNumbers(1 to UBound(List,1))
And then sort it (ascending) with
I'd then use ArrayOfRowNumbers to build the sorted arrayPlease Login or Register to view this content.
This technique can be applied to any kind of sort, Bubble, Quick, Heap.Please Login or Register to view this content.
This is what it would look like all put together. (Note the added optional SortDescending argument) and the testing routine.
@JBeaucaire, I suspect that the prof. is using Excel VBA as a platform to teach programming.Please Login or Register to view this content.
If its a class in Excel, then the built in sort is the goto technique (although Quick sort is faster for large data sets)
If Excel VBA is the language used to teach basic programming (as it is at my local college) then forbidding the use of methods in the Excel object model is good teaching.
Last edited by mikerickson; 03-28-2015 at 11:13 PM.
_
...How to Cross-post politely...
..Wrap code by selecting the code and clicking the # or read this. Thank you.
Good stuff, all.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks