+ Reply to Thread
Results 1 to 8 of 8

Implementing Binary Comparisions for a class objects

  1. #1
    Robert Mulroney
    Guest

    Implementing Binary Comparisions for a class objects

    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

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    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

  3. #3
    Robert Mulroney
    Guest

    Re: Implementing Binary Comparisions for a class objects

    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" wrote:

    >
    > 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
    >
    >
    > --
    > Leith Ross
    > ------------------------------------------------------------------------
    > Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
    > View this thread: http://www.excelforum.com/showthread...hreadid=488081
    >
    >


  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    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

  5. #5
    Patrick Molloy
    Guest

    RE: Implementing Binary Comparisions for a class objects

    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

    "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


  6. #6
    David Welch
    Guest

    Re: Implementing Binary Comparisions for a class objects

    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

  7. #7
    Peter T
    Guest

    Re: Implementing Binary Comparisions for a class objects

    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" <''''robert.mulroney@nh.org.au''''> wrote in message
    news:A15DD8E7-F165-4680-9BFD-1ADB563286F9@microsoft.com...
    > 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




  8. #8
    Robert Mulroney
    Guest

    Re: Implementing Binary Comparisions for a class objects

    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
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1