+ Reply to Thread
Results 1 to 10 of 10

Sorting arrays in VBA

  1. #1
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Sorting arrays in VBA

    These code samples sort values in the following order: NULLS (must be converted to 0-length strings), special characters (in numerical order from CHR() chart), numbers, upper case letters, lower case letters.

    Here's the code to sort a one-dimensional array:
    Please Login or Register  to view this content.
    If you want to sort an array with 2 dimensions, it's just like sorting rows of data on an excel sheet based on ONE column only (e.g. - sorting a table based on a column). For example, number of columns would be the 1st dimension and the number of rows would be the 2nd. Here's an example of sorting data on a sheet (a table of data) this way:
    Please Login or Register  to view this content.

  2. #2
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,443

    Re: Sorting arrays in VBA

    Someone has to ask: why Integer rather than Long? In the days of 4GB RAM floor for machines running Excel, is it REALLY vital to save 2 bytes per variable? I admit to decided bias on this point: I see no useful purpose served by using Integer or Single in VBA.

    Looks like bubble sort or shell sort, I can never keep then straight. Useful for small arrays, but O(N^2). As an alternative, quicksort, which is O(N log(N)).

    Please Login or Register  to view this content.
    Note: no error checking of v. If it isn't a 1D array, this throws runtime errors.

    Unlikely to be much execution time difference for N <= 100.

    As for sorting arrays of other dimensions, I've found it more useful to convert PxQ arrays into 1D arrays of P elements each of which is a 1D array of Q+1 elements with the sort key copied into the first element. Sort. Then convert the array of arrays back into a 2D array.

  3. #3
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: Sorting arrays in VBA

    Your question was.... Why integer instead of long?

    In my response is.... In this day why does it even matter? Considering the wide variance of knowledge level among all the people who ask questions here?you're posting this thread will do people a lot of good but for most of the people that I run into on a daily basis they won't understand the mathematics of it whatsoever. I understand all of that but everything you wrote is highly technical so I appreciate what you said. I won't add anything more to it.

    The other thing to say is that I'm not a master coder nor have I ever been, but rather I manage technical people and use their talents to put things together.

  4. #4
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,443

    Re: Sorting arrays in VBA

    Quote Originally Posted by vba_php View Post
    . . . why does it even matter? . . .
    Because arrays can have more than 32767 entries. Especially if the array in question were Application.Transpose(Range("A3:A100002")) or similar. Why write library routines for fractions of the problem domain?

    . . . for most of the people that I run into on a daily basis they won't understand the mathematics of it whatsoever. . . .
    Who reads this site's Tips and Tutorial forum over the course of the typical month? I figure most of them are likely to be sufficiently motivated to be able to understand quick sort. Heck, they probably already know it but just haven't seen a VBA implementation.

  5. #5
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: Sorting arrays in VBA

    Quote Originally Posted by hrlngrv View Post
    Because arrays can have more than 32767 entries. Especially if the array in question were Application.Transpose(Range("A3:A100002")) or similar.
    never heard of any attempt close to these claims in 20 years. But I don't work in the space of languages that are 30+ years old. I think we've hijacked in here enough. All your points are fine. thanks.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    50,934

    Re: Sorting arrays in VBA

    Adam - there is no hijacking going on. If you post tips and tutorials, then it's perfectly fine for other experienced and knowledgeable coders to challenge the advice given, and it's good for anyone looking at the tip to see the different approaches (makes for a more informed choice).

    Just because you started the thread does not mean that you can decide what should and should not be posted to it - I hope you understand this: threads on this forum are not 'owned'. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  7. #7
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: Sorting arrays in VBA

    I was saying that the discussion was not really relevant because we were essentially splitting hairs. I'll be back later.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    50,934

    Re: Sorting arrays in VBA

    The 'splitting hairs', as you call it, is interesting for anyone looking into this issue, so the discussion is entirely relevant and helpful!
    Last edited by AliGW; 12-19-2020 at 04:35 AM.

  9. #9
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,443

    Re: Sorting arrays in VBA

    ?

    Integer and Single types were needed 30+ years ago due to limited RAM and, all too often, software floating point processing.

    The largest positive value for an Integer in VBA is 2^15-1, or 32,767, the figure in my previous reply. The largest positive value for a Long is 2^31-1, or 2,147,483,647. Whole Excel columns have 1,048,576 rows, so Integer variables are unreliable for indexing arrays formed from large portions of workbooks. That was even the case in Excel 97 to 2003, when it had 65,536 rows, but still too much for Integer variables to index reliably.

    To repeat, if you're going to offer library routines, make them as general and robust as possible. That does, in fact, mean avoiding Integer type variables.

  10. #10
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: Sorting arrays in VBA

    Quote Originally Posted by hrlngrv View Post
    if you're going to offer library routines, .
    nothing about the code in the original post here has anything to do with libraries. no extra additions are necessary.

+ 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. [SOLVED] Sorting arrays by year
    By vtentarelli in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-06-2016, 07:32 PM
  2. [Solved]Sorting two arrays
    By zaoth in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-18-2014, 09:49 AM
  3. [SOLVED] Sorting through arrays of data
    By engineernoob in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-17-2013, 06:09 AM
  4. VBA arrays in a range: sorting
    By zeno1 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-27-2012, 02:28 PM
  5. Sorting arrays using VBA?
    By gryffin13 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-11-2012, 02:41 PM
  6. joining and sorting two arrays
    By Paul134 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-10-2008, 11:18 PM
  7. [SOLVED] VBA - Collections/Arrays/Sorting
    By William Benson in forum Excel General
    Replies: 5
    Last Post: 05-31-2005, 10:05 AM

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