How to read a collection name index value?

G

G Lykos

Greetings!

You can use a collection index value, for example Gizmos(13).Name, to get
the name of item 13. Am wondering if there is an equivalent direct
mechanism to use a collection item name to get its index value, along the
lines of Gizmos("SomeName").Index. If symmetrical functions (properties, I
guess, in VBA speak) existed, Gizmos(Gizmos("SomeName").Index).Name would
return "SomeName" - an identity property of sorts.

I suppose a brute-force approach would be to create a function doing a For
name/string comparison loop over 1 to Gizmos.count. Am hoping for something
more direct.

Thanks,
George
 
J

Joel

I haven't found one that works for everything. Sheets("sheet1") works but it
doesnn't work for shapes. I have jsut given up and use the good old for loop

found = false
for each Giz in Gizmos
if Gizmos.name = "SomeName" then
found = true
exit for
end if
next Giz

if found = true
etc
etc, etc
etc, etc, etc
end if
 
P

Peter T

Hi George,

Kind of depends on what you mean by a collection and if items in the
collection have a name property. Eg with sheets you could do this -

Set objShts = ActiveWorkbook.Worksheets
sName = objShts(objShts(1).Name).Name
idx = objShts(sName).Index

If your collection is declared "As Collection", there is no direct way to
return a 'Key' from an item's index. However there's an API approach that
can return an array of all the Key's in a collection.

Regards,
Peter T
 
C

Chip Pearson

You can't do it with a Collection object without looping. If, however, you
can use a Dictionary object instead of a Collection object, you can do
something like the following,

Sub BBB()

Dim D As Scripting.Dictionary
Dim WhatKey As String
Dim V As Variant
Set D = New Scripting.Dictionary

D.Add Key:="a", Item:="aaa"
D.Add Key:="b", Item:="bbb"
D.Add Key:="c", Item:="ccc"

WhatKey = "c" ' what Key do you want to find
V = Application.Match(WhatKey, D.Keys, 0) ' 1-based, not 0-based
If IsError(V) = True Then
Debug.Print "Key Not Found: " & WhatKey
Else
V = V - 1 ' change 1-based Match result to 0-based index
Debug.Print "Index Of Key: (" & WhatKey & "):" & CLng(V) & " (index is
0-based)"
V = D.Items(Application.Match(WhatKey, D.Keys, 0) - 1)
Debug.Print "Item of Key (" & WhatKey & "): " & V
End If

End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)
 
A

Alan Beban

G said:
Greetings!

You can use a collection index value, for example Gizmos(13).Name, to get
the name of item 13.

In fact, you use Gizmos(13), not Gizmos(13).Name.

Am wondering if there is an equivalent direct
mechanism to use a collection item name to get its index value, along the
lines of Gizmos("SomeName").Index. . . .

If you CAN use a Dictionary object rather than a Collection object, as
suggested by Chip Pearson, then you can create it with index numbers
(i.e., keys) from 1 to the number of items in the object (as are the
index numbers in a Collection object) and then simply use

Application.Match(itemName, Gizmos.Items, 0)

This presumes, of course, as your original question tends to suggest,
that there is only one occurrence of the name whose index you are seeking.

Alan Beban
 
A

Alan Beban

Alan said:
In fact, you use Gizmos(13), not Gizmos(13).Name.

Am wondering if there is an equivalent direct



If you CAN use a Dictionary object rather than a Collection object, as
suggested by Chip Pearson, then you can create it with index numbers
(i.e., keys) from 1 to the number of items in the object (as are the
index numbers in a Collection object) and then simply use

Application.Match(itemName, Gizmos.Items, 0)

This presumes, of course, as your original question tends to suggest,
that there is only one occurrence of the name whose index you are seeking.

Alan Beban
If you already have a Collection object that was declared as Variant or
Object, you can convert it to a 1-based Dictionary object by first, in
the VB Editor clicking Tools|References and checking Microsoft Scripting
Runtime, then running

Set Gizmos = ConvertCollToDict(Gizmos)

with

Function ConvertCollToDict(Coll)
Dim q As Dictionary, i As Long
Set q = New Dictionary
For i = 1 To Coll.Count
q.Add Item:=Coll(i), Key:=(i)
Next
Set ConvertCollToDict = q
End Function

Then Application.Match(itemName, Gizmos.Items, 0) will give you the
index number of the item named "itemName".

Alan Beban
 
D

Dana DeLouis

If IsError(V) = True Then

Here's another way to test...

Sub Demo()
Dim d As Scripting.Dictionary
Set d = New Scripting.Dictionary
d.Add "a", "aaa" 'Add key - Items
d.Add "b", "bbb"
d.Add "c", "ccc"

If d.Exists("c") Then
MsgBox "Index is: " & WorksheetFunction.Match("c", d.Keys, 0)
End If
End Sub
 
A

Alan Beban

Dana said:
Here's another way to test...

Sub Demo()
Dim d As Scripting.Dictionary
Set d = New Scripting.Dictionary
d.Add "a", "aaa" 'Add key - Items
d.Add "b", "bbb"
d.Add "c", "ccc"

If d.Exists("c") Then
MsgBox "Index is: " & WorksheetFunction.Match("c", d.Keys, 0)
End If
End Sub
Well, aside from the fact that Dana DeLouis's approach gives 3 as the
index for "c" and Chip Pearson's approach gives 2 (which is correct),
isn't the Op's assigned exercise to find the Index of a specified Item
(analogous to an Item of a Collection) rather than of a specified Key?

Alan Beban
 
A

Alan Beban

Dana said:
Here's another way to test...

Sub Demo()
Dim d As Scripting.Dictionary
Set d = New Scripting.Dictionary
d.Add "a", "aaa" 'Add key - Items
d.Add "b", "bbb"
d.Add "c", "ccc"

If d.Exists("c") Then
MsgBox "Index is: " & WorksheetFunction.Match("c", d.Keys, 0)
End If
End Sub

The range contains

a b c
d e f
g h i

The function converts the Collection object (Gizmos)into a Dictionary
object with Keys 1 through 9.

Sub abtest4()
Dim arr(), arr1(), Gizmos
Dim rng As range
Set rng = range("G1:I3")
Set Gizmos = New Collection
For i = 0 To rng.Count - 1
Gizmos.Add Item:=rng(i + 1)
Next
Set Gizmos = ConvertCollToDict(Gizmos)
MsgBox Gizmos(2) '<-------Displays b
MsgBox Gizmos.Items(2) 'Displays error message--"Property let
'procedure not defined and property get procedure did not return an
'object" WHAT'S GOING ON WITH THIS?
End Sub
Function ConvertCollToDict(Coll)
Dim q As Scripting.Dictionary, i As Long
Set q = New Scripting.Dictionary
For i = 1 To Coll.Count
q.Add Item:=Coll(i), Key:=(i)
Next
Set ConvertCollToDict = q
MsgBox q(2) '<-----Displays b; i.e., the Item corresponding to Key 2
MsgBox q.Items(2) '<-------Displays c; i.e., the Item corresponding
'to the No.2 Item in the 0-based indexing system
End Function
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top