Thank-you David, that's a good solution.

Just for the record:

- an array would be a easier solution but my Table class does more then just
store data. Some of the table fields are calculated when required using the
get and let functions. As thie information can be calculated, and may not be
required, I don't store it and only calculate it when required.

- A simple excel SORT is impossible in this situation. I have several
tables which consists of a title row, followed by an undetermined number of
invoices, and finally a closing balance "total" line. Excel's sort function
isn't set-up to maintain groups of related lines together.

- What I'm doing isn't as complicated as you all seem to think. I guess you
could say I've recreated a relational system, I prefer to think of it as
efficient memory management. The original data source is in a remote
location, although it would be possible to re-query the database and return
the information in the required order, it is more efficient to maintain the
data in a proper structure on the client side. Otherwise I'll have all the
data processing happening on my server and a nice fast client desktop sitting
there doing nothing.

finally, many thanks to you all for your help it is very much appreciated.


- Rm





"David Welch" wrote:

Robert Mulroney wrote:
I have implemented a class in VBA and I'm storing multiple instances of the
class in a collection. I would now like to sort the objects in the collection
based on one of the class properties. Which property we sort by will be
determined at run-time.

I've written a procedure that can sort items in a collection but it relies
on using binary operators (, <, =, ect) to rate one collection item against
the next. That's all fine if you're only storing primitive types in the
collection, I have a homogeneous collection of objects how can I compare one
to the next?

I have seen people implement an "=" method for a class in C#.net (no I can't
remember how they did it), but can you do anything like that in VBA.

- Rm
Unfortunatly you can't implement operators (eg +,-,<,) in VB. But you
could write an accessor class which would return a value from an object,
and then sort by that value. eg

AccessorValue.cls
-----------------

function Value(byval obj as object) as variant
Value = obj.Value
end function

then pass the class to your sort routine.

set SortedCollection = SortCollection(Collection, new AccessorValue)

where your SortCollection routine uses the Value returned by the
accessor object to do the comparision, eg:

rather than
If Obj1.Value < Obj2.Value Then

you do:
If Accessor(Obj1.Value) < Accessor(Obj2.Value) Then

And so on.

---
Dave