Implementing Binary Comparisions for a class objects

[Robert Mulroney]

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

 

[Leith Ross]

Hello Robert,

If you really have created an Collection Object (Clollection Class) to
hold this information, why do you want to sort it? The purpose of the
collection is to allow random access of items in the collection. This
methodology removes the need to keep the items sorted. Each
instantiation of the object will create a unique ID. Perhaps if you
really need to sort the objects, rather than using properties, the IDs
would be faster and easier.

Sincerely,
Leith Ross

 

[Robert Mulroney]

Yep, the point is: I have a class and collection that contains a number of
instances of that class. I want to be able to sort the class objects in the
collection in the by any of it's attributes.

So more specifically, the class represents a table that contain outstanding
invoice data for a particular vendor. The collection contains a number of
tables (one for each vendor), at run time I'm outputting these tables from
the collection in an unspecified order. I want to provide my users with the
ability to sort the output by any of the following: Vendor ID, Vendor Name,
Amount Owing, percentage change in amount owing from one month to the next.

By default I'm outputting the table in Vendor ID order, and as this is
unique I'm using it as the Key to collection. The purpose of sorting the
collection is so I can loop through the items without using the key. ie
mycollection.items(1) : In the order that the user chooses as run-time.


- Rm

 

[Leith Ross]

Hello Robert,

It appears you are creating a relational database. You could save
yourself headaches by using Pivot Tables or switch to Access. The
Direction you are heading now will make maintaing the code very
difficult later on. As you know, customers always want the program to
do something else. My choice would be the Pivot Tables.

Sincerely,
Leith Ross

 

[Patrick Molloy]

You're really re-inventing the wheel here.
I've read your conversations with Leith Ross.
As I see it, you mayu want to drop the contents of a class into a sheet,
sorted by whatever field the user selects.
So, why not drop the data into a sheet first and then use Excel's SORT
method -- youcan still use the user's selected field. This sort is already
pretty well optimised fro you.
Also, if you want to filter for a particular ID, or filter it out, this is
more easily achieced once the data is on a sheet. If you set ScreenUpdatign
to FALSE at the start of your process, it will run much faster, and there's
no flashing for the user to see...just set it to TRUE again at the end of the
process.

HTH

 

[David Welch]

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.

 

[Peter T]

If, as you have said elsewhere in this thread, you don't need "key" maybe
hold your class's in an array. You can add to this with Preserve and sort as
you would any other array.

Or instead of sorting the class array you could maintain an index array.

indexArr(0,ubound(clsArr)) = property to sort on
indexArr(1,ubound(clsArr)) = ubound(clsArr)

clsArr(indexArr(1,n).myclassthing

Actually you could also maintain an index array to refer to your collection
items.

Regards,
Peter T

 

[Robert Mulroney]

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