Collection Look Up - By Key in an Excel VBA App.

[vmegha]

Hi,

I have used a collection to store a set of Objects that I need to look
up by the Key value...

Like

Dim c As Collection
Set c=New Collection
c.Add Obj, Obj.Key

Neither of these seem to work-
Key = c(Obj.Key)

Or

Key = c.Item(Obj.Key)

Although the help on the VBA Collection object behaviour seems to
support either of these two types of Look Up.

Is there some Version / Application Dependency that i might be mnissing
here?

Also I had to replace the collection with a dictionary for this to
work. My observation has been that "Add" into a Dictionary is slower
compared to a collection. The Lookup I havent been able to compare,
since I havent been able to Look up an Obj by key from a collection.

Help / Ideas, Please?

Thanks in Advance,
Megha.

 

[Tushar Mehta]

What are you trying to do? Normally, once one stores an object in a
collection, you have to *know* the key through some independent process
to retrieve it.

Yes, you can make your example work but it makes little sense. Here's
why.

First, to make it work you should use

c.add obj,obj.key
set obj=c(obj.key)

Why doesn't it make sense? If you get the key from the object, you
already have a variable pointing to the object. Why bother retrieving
it from the collection?

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[vmegha]

This was just an example to say

Public c As Collection

Sub A
Dim Obj As Class1
Set Obj = New Class1
.... Some settting of values and key
Set c=New Collection
c.Add Obj, Obj.Key
End Sub

Sub B( Key As String)
Dim Obj As Class1
Set Obj=c(Key) ---> this is not working
'Set Obj=c.Item(Key) --Neither is this
End Sub

-Megha

 

[Tushar Mehta]

That's a technique that I've implemented any number of times. It
works.

If you would like someone to help you, you need to provide the code
that doesn't work, not just unconnected snippets.

What is the type of Class1's Key? What is the value of Obj.Key when
you add the object to the collection?

How are A and B related? When and in what sequence are they called?
What is the value of B's argument Key when it doesn't work?

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions