VBA: how do I sort a collection?

Question

Does anyone know how to sort a collection in VBA?

Answer 1

There is no native sort for the Collection in VBA, but since you can access items in the collection via index, you can implement a sorting algorithm to go through the collection and sort into a new collection.

Here's a HeapSort algorithm implementation for VBA/VB 6.

Here's what appears to be a BubbleSort algorithm implementation for VBA/VB6.

Answer 2

Collection is a rather wrong object for sorting.

The very point of a collection is to provide very fast access to a certain element identified by a key. How the items are stored internally should be irrelevant.

You might want to consider using arrays instead of collections if you actually need sorting.

---

Other than that, yes, you can sort items in a collection.
You need to take any sorting algorithm available on the Internet (you can google inplementations in basically any language) and make a minor change where a swap occurs (other changes are unnecessary as vba collections, like arrays, can be accessed with indices). To swap two items in a collection, you need to remove them both from the collection and insert them back at the right positions (using the third or the forth parameter of the Add method).

Answer 3

http://www.dailydoseofexcel.com/archives/2004/06/02/sort-a-collection/

uses a bubble sort