Searching Through a Collection

[Dan Jones]

Can anyone recommend a better way to search through a collection rather than
a

for each item in items

do something

next item

Say I know which item in a collection I am interested in but I want to know
a particulary property about that item. Can I set a variable to that item
and then refer to that items' property,

for example,

set myitem = items.index(45)

strName = myItem.Name

set myitem = nothing

Any suggestions would be greatly appreciated.

Thanks
Dan Jones

[J.E. McGimpsey]

If you know which item you're interested in you can simply address that
item:

strName = items(45).Name

In article <LlQS6.61930$%i7.47...@news1.rdc1.sfba.home.com>,
"Dan Jones" <djo...@lanset.com> wrote:

--
J.E. McGimpsey ar...@zptvzcfrl.pbz
ROT13 encoding, decode for real mail

[Do Bee]

You can do what you are asking, but it would be more like this:

'Assigns item 45 of Object to object variable
Set myitem = Object.Item(45)
'assigns the name of the item to a string variable
strName = myitem.Name
'destroys object
Set myitem = Nothing

For example:

Set myitem = Workbooks.Item(1)
strName = myitem.Name
Set myitem = Nothing

One caution is that the index number may not always be assigned to the same
object. In this case, Item(1) is only the number assigned a certain workbook
among those workbooks that are currently open. (In my case it would be
Personal.xls) However, I can't depend on Item(1) always being the same open
workbook.

"Dan Jones" <djo...@lanset.com> wrote in message
news:LlQS6.61930$%i7.47...@news1.rdc1.sfba.home.com...

[Dan Jones]

My example wasn't the best

What if I have a collection like the following

item.id
item.name
item.last name

And I know the item.name value, but I want to get the item.last name value

Can I quickly get to the item within the colleciton that matches item.name
without having to use a for each loop? In this case I don't know the item's
particular index within the collection, I just know the value of one of the
items property.

Thanks
Dan
"J.E. McGimpsey" <ar...@zptvzcfrl.pbz> wrote in message
news:arjf-464242.1...@msnews.microsoft.com...

[Chip Pearson]

Dan,

When you add an item to the Collection, use the Key property.
Then, you can access that item in the Collection using the Key.
Keys must be strings, and must be unique.

Suppose you have class named Class1 with the following code:

Public Name As String
Public Value As Integer

Then, in your standard code module,

Sub AAA()

Dim Coll As Collection
Dim C As Class1
Dim Res As Class1
Dim N As Integer
Set Coll = New Collection
'
' Load some objects into the collection
'
For N = 1 To 10
Set C = New Class1
C.Name = "Object_Name_" & Format(N)
C.Value = N * 100
Coll.Add Item:=C, key:="Key_Name_" & Format(N)
Next N
'
' Get the object whose KEY is "Key_Name_5"
'
Set C = Coll("Key_Name_5")
'
' Print its properties
'
Debug.Print "Item -- Name: " & C.Name & " Value: " & C.Value
End Sub

Unfortuately, there is no way to read a Key value, given the
index number. (This is the only thing I can think of that is a
'write-only' property.) You can write your own code to simulate
this ability, but it is not built in to the Collection object.
Odd, I think.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com

"Dan Jones" <djo...@lanset.com> wrote in message
news:LlQS6.61930$%i7.47...@news1.rdc1.sfba.home.com...

[Chip Pearson]

Dan,

Expanding on my reply based on your post here, just use the same
value for both the KEY of the collection and the Name property of
your object. E.g.,

For N = 1 To 10
Set C = New Class1

C.Name = "Name_" & Format(N)

C.Value = N * 100

Coll.Add Item:=C, key:=C.Name
Next N

Of course, the Key must be unique, which implies that your Name
property must be unique among all instances of the class.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com

"Dan Jones" <djo...@lanset.com> wrote in message

news:bzQS6.62000$%i7.47...@news1.rdc1.sfba.home.com...

[Do Bee]

This is a sample statement to illustrate what you are trying to do. I have a
workbook that is currently open called book2.xls. I want to get the number
of sheets in that workbook. I don't have to loop through the collection of
all open workbooks because I know the name of the book. I can just do this:

SheetCount = Workbooks("book2.xls").Sheets.Count

Using your scenario, it would be something like:

ItemLastName = item.name(ItemName).LastName

"Dan Jones" <djo...@lanset.com> wrote in message
news:bzQS6.62000$%i7.47...@news1.rdc1.sfba.home.com...

[Chip Pearson]

Just to clarify, based on my own reply and those of others......

With collections, there are only two ways to access a particular
item: its index number or its Key value. Anything else requires
a loop. Suppose your class has three properties:

Public FirstName As String
Public LastName As String
Public IDNumber As Long

You can use exactly one of those as the Key of the collection
object. If you do that, and that value is unique within the
collection, you can access that item of the collection via the
key access method. The name of the property is irrelevant. E.g.,

Coll("Pearson").IDNumber

However, there is not way to reference an object in a collection
by some arbitrary property of the object. If you are looking for
something like

Coll(IDNumber:=12345).LastName

you can't do it. That's not the way collections work. A
collection really has no internal "knowlege" of the individual
items it contains. The individual items are really just 'black
boxes' to the collection. The Collection can't look inside.

A Collection is like a FedEx truck. It contains boxes. You can
ask for the 10th package in the truck (the index number) or you
can ask for the package with some tracking number (the Key), but
you can't ask for package containing the steaks from Omaha Beef.
FedEx can't look inside the packages, and neither can a
Collection object. For this sort of thing, you must use a loop.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com

"Chip Pearson" <ch...@cpearson.com> wrote in message
news:eqHYLeS7AHA.2112@tkmsftngp05...

[Dan Jones]

Thank you everyone who replied. Usually I have to wait for day or so, but
this was just minutes!
Special Thanks to Chip Pearson, your explanation of how collections work
made sense. I am re-thinking my application to make use of the keys.

Dan Jones

"Chip Pearson" <ch...@cpearson.com> wrote in message

news:OI5EGcS7AHA.1884@tkmsftngp05...

[Chip Pearson]

> Special Thanks to Chip Pearson, your explanation of how
collections work

That's why we're here. I'll be looking for the steaks on the
FedEx truck any day now, right?

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com

"Dan Jones" <djo...@lanset.com> wrote in message

news:kgTS6.62517$%i7.47...@news1.rdc1.sfba.home.com...

[Chip Pearson]

> (This is the only thing I can think of that is a
> 'write-only' property.)

Excel XP notwithstanding, of course.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com

"Chip Pearson" <ch...@cpearson.com> wrote in message
news:OI5EGcS7AHA.1884@tkmsftngp05...

[dermot]

I spent part of my weekend trying to use a collection for the same thing, as a
fast way of looking up an item, then wanting to use information about that item
which was hard to store or reference in a collection.

I ended up setting the value of each collection item to its index sequence, ie
the first collection item had value 1, etc.

C.Add "1","Calvin"
C.Add "2","Hobbes"
C.Add "3","Comic"
...

Then I simply used the key to find the index for my item, ie

C("Hobbes") = "2" so I know it is the second item

and I had separate arrays with information about that item. The collection only
held the undex. That is very fast.

Regards

Dermot Balson

[Dana DeLouis]

I know I am joining the party late, but just another option could be the use
of a Dictionary Object.
I find that this object has more options to work with, and it is easy to
use.
Using this, in some situations, can save a lot of programming time.
As Chip mentioned, you want to work with Unique items.
There could be many people with the same last name (ie Smith, etc)
You will have to find a way to work around this. Using an employee ID would
appear to work.
Just for an idea, this makes a unique Key table based on your desire for a
lookup on Last Names.
If anyone is interested, here are some ideas that the Collection object may
have a hard time with.
Again, just for a fun demonstration:

Sub Demo()
'// By: Dana DeLouis: da...@msn.com
Dim dic1 As Dictionary
Dim dic2 As Dictionary
Dim obj As Variant
Dim UniqueID As Long

Set dic1 = New Scripting.Dictionary
Set dic2 = New Scripting.Dictionary

'// ID, FirstName, LastName, Age
'// Key is ID (most likely this is unique)
dic1.Add 101, Array(101, "Chip", "Pearson", 21)
dic1.Add 102, Array(102, "Do", "Bee", 22)
dic1.Add 103, Array(103, "Dan", "Jones", 23)
dic1.Add 104, Array(104, "Dana", "DeLouis", 24)

'// Make a LastName <-> Key relationship
'// Objects are 0 Based.
For Each obj In dic1.Items
dic2.Add obj(2), obj(0)
Next

'// Ok, lets find out about Mr. Pearson

'// Does he exists?
If Not dic2.Exists("Pearson") Then Exit Sub
UniqueID = dic2("Pearson")

Debug.Print
Debug.Print "Information on 'Pearson'"
Debug.Print "id: " & dic1(UniqueID)(0)
Debug.Print "FirstName: " & dic1(UniqueID)(1)
Debug.Print "LastName :" & dic1(UniqueID)(2)
Debug.Print "Age: " & dic1(UniqueID)(3)

'// Ups! His ID should be #1
'// since he has helped so many people :>)

obj = dic1(101)
obj(0) = 1

dic1.Remove (101)
dic1.Add obj(0), obj

dic2.Remove (obj(2))
dic2.Add obj(2), obj(0)

'// Ups! His Age should be 29
'// ...Demonstration only :>)

obj = dic1(1)
obj(3) = 29
dic1.Remove 1
dic1.Add obj(0), obj

'// Lets see if I got it Right?
If Not dic2.Exists("Pearson") Then Exit Sub
UniqueID = dic2("Pearson")

Debug.Print
Debug.Print "Information on 'Pearson'"
Debug.Print "id: " & dic1(UniqueID)(0)
Debug.Print "FirstName: " & dic1(UniqueID)(1)
Debug.Print "LastName :" & dic1(UniqueID)(2)
Debug.Print "Age: " & dic1(UniqueID)(3)

Set dic1 = Nothing
Set dic2 = Nothing
End Sub

'= = = = = = = = = = = = = = = = = = = = = = = = = = = =

The Results I got are:

Information on 'Pearson'
id: 101
FirstName: Chip
LastName :Pearson
Age: 21

Information on 'Pearson'
id: 1
FirstName: Chip
LastName :Pearson
Age: 29

'= = = = = = = = = = = = = = = = = = = = = = = = = = = =

"Chip Pearson" <ch...@cpearson.com> wrote in message

news:e9mN9mS7AHA.1852@tkmsftngp04...