How to read a collection name index value?

[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

 

[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

 

[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

 

[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)

 

[Alan Beban]

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

 

[Alan Beban]

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

 

[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

 

[Alan Beban]

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

 

[Alan Beban]

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

 

[Chip Pearson]

See http://www.cpearson.com/Excel/CollectionsAndDictionaries.htm for a whole
slew of functions for working with collection and dictionaries including
converting one to the other.


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