Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to read a collection name index value?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to read a collection name index value?
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 "G Lykos" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to read a collection name index value?
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 "G Lykos" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to read a collection name index value?
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) "G Lykos" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to read a collection name index value?
G Lykos wrote:
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to read a collection name index value?
Alan Beban wrote:
G Lykos wrote: 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 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to read a collection name index value?
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 -- Dana DeLouis "Chip Pearson" wrote in message ... 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) "G Lykos" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to read a collection name index value?
Dana DeLouis wrote:
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 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to read a collection name index value?
Dana DeLouis wrote:
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 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to read a collection name index value?
See http://www.cpearson.com/Excel/Collec...ctionaries.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) "Alan Beban" wrote in message ... Alan Beban wrote: G Lykos wrote: 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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rule to recall collection lower bound index ? | Excel Programming | |||
How can a file be converted from Read-Only to Read/Write | Excel Discussion (Misc queries) | |||
Need Help with Set Object = Collection(index) | Excel Programming |