Is there a quick way to compare collections in vba?

Hi guys,

I am generating two lists of collections. I am wondering if there is a way to quickly determine if both collections are the same? Is there an inbuilt function?

Otherwise I suppose I will have to create a few checks myself, if that is the case, then I'd probably do the following, in the following order.
1/ check both are the same size
2/ Sort both collections
3/ check both collections are the same, index value by index value.


Any opinions? Any suggestions?

Cheers,
Jimmy

Originally Posted by shg

Not sure how you're going to do that, because there's no way to get the keys from a collection.

My understanding is you need to generate keys while creating the collection, or when adding to a collection. I could do that, but i don't see the point in doing that if for this case.

Originally Posted by shg

And if the items were anything other than simple scalar variables, it would be very difficult.

Nope, easy:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I only have one problem...
What are you on about?


I guess that means there is no quick method for checking collections?



And if the items were anything other than simple scalar variables, it would be very difficult.

A dictionary would resolve the first problem, but not the second.[/QUOTE]

Originally Posted by shg

You are sorting them by item, and your items are strings, which are simple scalar variables.

Ohhhh, yep. I see.

Originally Posted by shg

But a collection can contain almost anything as items -- arrays, ranges, other objects, anything except a user-defined type. You would be hard-pressed to sort by that, and it would be harder yet to compare them.

Yep, yep. makes sense.

Originally Posted by shg

I assumed you wanted to sort by key (which is always a string for a collection if there is a key), which there is no way to do.

Yep, that sounds familiar/makes sense to me.

ep, yep. makes sense.

Originally Posted by shg

For your simple case, after sorting, verify that the counts are the same and then compare item by item.

That's what I am doing now. However let me ask you another question:
I remember doing an experiment with collections and their indexes about 6 months ago (cant find the code); I remember concluding that "index numbers aren't fixed and may switch haphazardly in any order".
Is there a way to know when collection would "randomize"? I have a feeling it might be whenever a new item is added to a collection, or when one is removed from a collection? But I could be wrong.

My big worry about that is, if I sort them, then the index numbers are all nice and neat.. How long (or until when) can I rely on the collect staying sorted before the index number's become "randomize"?

I hope that makes sense, if not I can try explaining it again.

Cheers,
Jimmy