+ Reply to Thread
Results 1 to 7 of 7

Help With VBA - BUBBLE SORT

  1. #1
    Registered User
    Join Date
    03-28-2015
    Location
    Toronto
    MS-Off Ver
    2010
    Posts
    2

    Help With VBA - BUBBLE SORT

    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??

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Help With VBA - BUBBLE SORT

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

  3. #3
    Registered User
    Join Date
    03-28-2015
    Location
    Toronto
    MS-Off Ver
    2010
    Posts
    2

    Re: Help With VBA - BUBBLE SORT

    I know bubble sort is not ideal for this but my prof wants it done that way

    the code provided is:

    Please Login or Register  to view this content.
    But something is missing as it doesn't take sort for the adjacent values.
    Last edited by JBeaucaire; 03-28-2015 at 05:43 PM.

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Help With VBA - BUBBLE SORT

    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

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Help With VBA - BUBBLE SORT

    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

  6. #6
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Help With VBA - BUBBLE SORT

    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

    Please Login or Register  to view this content.
    I'd then use ArrayOfRowNumbers to build the sorted array

    Please Login or Register  to view this content.
    This technique can be applied to any kind of sort, Bubble, Quick, Heap.

    This is what it would look like all put together. (Note the added optional SortDescending argument) and the testing routine.

    Please Login or Register  to view this content.
    @JBeaucaire, I suspect that the prof. is using Excel VBA as a platform to teach programming.
    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.

  7. #7
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Help With VBA - BUBBLE SORT

    Good stuff, all.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Need help editing a Bubble Sort UDF
    By tjk321 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-10-2015, 11:41 AM
  2. [SOLVED] Slow bubble sort
    By Oppressed1 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-03-2012, 07:33 PM
  3. Edit bubble behind another bubble in bubble chart (2007)
    By JayUSA in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 07-14-2009, 03:10 PM
  4. Problems with Bubble Sort on 2D Array
    By ExcelMonkey in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 02-20-2006, 12:55 PM
  5. Replies: 0
    Last Post: 02-20-2005, 04:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1