Friday, October 29, 2010

Quicksort for VBA array of custom objects

Can you believe that there is no sort function for the collection object in Access VBA? Anyway, I have dumped the collection for a typed array to avoid some of the overhead of type casting variants everywhere. (Long story involving a profiler and a lot of time which finally resulted in finding a different source for the bug that was causing the slow down.)

Back to the main thread. I went looking for a good implementation of a sorting algorithm in VBA. After a couple of absolutly spectacular crappy implementations I found this one.

http://www.java2s.com/Code/VBA-Excel-Access-Word/Data-Type/QuickSort2.htm

Which is short, simple and elegant. In comparison to some of the other multi-page examples I found its really ... beautiful.

Anyway, the first thing I did was refactor out the comparison operation and the swap operation, so I could apply the operation to an array of custom objects without adding clutter to the quicksort sub.  By extracting the comparison operation, I can now sort the objects in the array on a variety of properties.  If I get bored I will implement both ascending and decending sort but I don't need it right now.

The most painful part of this is not being able to write a template for the code and make type independent. (Anyone mentions VBA variants as being equivalent and they are outa here ...)

Just thought I would share...

No comments:

Post a Comment