# Sorting arrays in VBA

1. ## 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:
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:

2. ## 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)).

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. ## Re: Sorting arrays in VBA

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. ## Re: Sorting arrays in VBA

Originally Posted by vba_php
. . . 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. ## Re: Sorting arrays in VBA

Originally Posted by hrlngrv
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. ## 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.

7. ## 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. ## 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!

9. ## 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. ## Re: Sorting arrays in VBA

Originally Posted by hrlngrv
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.

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

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