Sorting an array of strings into alphabetical order

http://www.cpearson.com/excel/SortingArrays.aspx

In my experience excel will be far quicker than code alone given the size of the array you want to sort.

Hi welchs,

There are lots of sorting algorithms that have been developed. I believe putting the data into Excel and letting it sort (using C code) is much faster than trying to do it yourself in VBA. See some algorithms at:
http://www.vbadeveloper.net/sortingv...rtionquick.pdf
https://social.msdn.microsoft.com/Fo...n?forum=isvvba

Kyle, a few questions if I may:

1.

I reckon that quicksort would be faster than using an arraylist though

Why?

(EDIT: Thanks for the timings; that's interesting and surprising.)

It appears that the Sort method is not stable (like QuickSort). Sorting "The quick brown fox jumped over the lazy dog's back" returns as its last two items "the" and "The"

2. How do you set case sensitivity for the sort?

And ...

I believe all of those System.Collections thingies are in mscoree.dll. What reference would you set for early binding?

Interesting, thank you.

@Kyle123 I'd be interested if you could repeat your analysis but turn off screenupdating / calculation etc - all the things you'd normally do to speed up vba in the worksheet sort.

ahh spotted ta - your worksheet sort seems to be extremely slow.... What about using a variant data type with a second dimension so you can do away with the transpose that you have to run twice?

I normally find 15000 records (typically people name data) is my tipping point to worksheet sort from straight VBA, but i guess depends on what your data looks like.

I guess trial and error is the answer!

If you're sorting 2D data, it's probably important that you use a stable sort.

Upon reading a little further, I was wrong on the internal structure of the arraylist. It is actually comprised of an internal array which essentially ReDim Preserves when you add elements* - this explains why it is slow adding elements.

Further reading available below:

*The redim preserve is more efficient than the traditional method:

Originally Posted by http://blogs.msdn.com/b/bclteam/archive/2004/12/01/273454.aspx

When the internal array is not large enough to hold all items (happens when new items are added to an ArrayList object,) a new array with double size of the original array will be created. All the items in the ArrayList will be copied from the old Array to the new Array. The new Array will be used from here on and the old array will be reclaimed by garbage collector sooner or later.

EDIT:
Having read the article about the inner workings, one would assume that specifying the capacity of the ArrayList in advance of population would increase the performance:

Please Login or Register  to view this content.

This doesn't unfortunately doesn't appear to have any real impact on performance. FWIW the actual sort and return of array is faster than quicksort as you'd expect - the issue is still the population

FWIW, you can set a reference to mscorlib.dll and then just declare as ArrayList.

Welchs101, hopefully you have your solution now. There has been several good discussions in this thread. You asked for sorting an array, not a worksheet range, so you have several methods to pick from now. I tried a VBA Counting Sort routine but it did not work for a string array. Ultimately, my guess is that you would want to write the sorted data to an Excel range or an external data file which would change the speed numbers.

Romperstomper, I could not set a reference to the mscorlib.dll file to see if early binding increased speed for that method. I tried regsvr32 to register it but it did not work. Maybe a regasm would set the reference listing.

Using several sorting methods in the attached file, I found this below. I included an ADO method but did not take time to finish it for testing. ADO methods tend to be a bit slow. Note that the jscript method was the 2nd fastest while the quick sort was the fastest. Real world data should be used for testing speeds though. When there are duplicates, that can cause problems for some routines. In the jscript method, if there is a value of error for an element, it will fail. From the data below, for really large sort tests, be sure to remove the really slow ones first.

Lastly, keep in mind that speed is not everything. Stability is a more critical.

Please Login or Register  to view this content.