Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Collections
Hi All,
Is there a method to get the used key back from a collection? Suppose you have some (very stupid code) like this: set c = new collection c.add item:=1, key:="A" c.add item:=2, key:="B" c.add item:=3, key:="C" How to know which key was used for c(1), c(2)... TIA. Wkr, JP |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Collections
The idea of assigning a Key is so that you can use it in place of knowing or
having to track the position number of your item within the collection (especially since the position number can change with deletions of items ahead of the item), so the assumption is that you *know* which Key you are using to track any particular item. To the best of my knowledge, there is no way to retrieve the Key assigned to an item in code... and, quite frankly, I can't think of a scenario where being able to do so would be needed. Can you explain why you think you would need to do what you have asked (perhaps we can suggest an alternative to you)? -- Rick (MVP - Excel) "JP Ronse" wrote in message ... Hi All, Is there a method to get the used key back from a collection? Suppose you have some (very stupid code) like this: set c = new collection c.add item:=1, key:="A" c.add item:=2, key:="B" c.add item:=3, key:="C" How to know which key was used for c(1), c(2)... TIA. Wkr, JP |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Collections
Hi Rick,
Thanks for your reply. Trying to assist someone over here with some VBA code (generating 1000 random numbers between 1 and 100 and take the top 20), I used the random number as key, and as item the number of times the same random number was generated. My first thought was, if I take each item from the collection and if I should read the key, I know - per key - the number of occurences. Your answer: "... the assumption is that you *know* which Key ..." did me think a bit further. You are completely right herein and I do know the keys: they are between 1 and 100. So I can walk through to get the occurences. A little bit ashamed to make such beginners fault, but thanks for your time and to bring me back on the right track. Wkr, JP "Rick Rothstein" wrote in message ... The idea of assigning a Key is so that you can use it in place of knowing or having to track the position number of your item within the collection (especially since the position number can change with deletions of items ahead of the item), so the assumption is that you *know* which Key you are using to track any particular item. To the best of my knowledge, there is no way to retrieve the Key assigned to an item in code... and, quite frankly, I can't think of a scenario where being able to do so would be needed. Can you explain why you think you would need to do what you have asked (perhaps we can suggest an alternative to you)? -- Rick (MVP - Excel) "JP Ronse" wrote in message ... Hi All, Is there a method to get the used key back from a collection? Suppose you have some (very stupid code) like this: set c = new collection c.add item:=1, key:="A" c.add item:=2, key:="B" c.add item:=3, key:="C" How to know which key was used for c(1), c(2)... TIA. Wkr, JP |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Collections
Hi. I'm not sure this is the best method to do this, but here are some
general ideas. In these examples, look for the "Keys" and "Items". Sounds like that's what you are looking for. I threw this together quickly, so I hope I didn't make too many mistakes. Sub Demo_Dic() Dim D Dim k Set D = CreateObject("Scripting.Dictionary") D.Add "Athens", 1 'Add some keys - items D.Add "Belgrade", 2 D.Add "Cairo", 3 '// The key for #2 k = D.Keys MsgBox "Second Key is: " & k(2 - 1) End Sub Sub Demo2() Dim D Dim k Dim n Dim j Dim v Set D = CreateObject("Scripting.Dictionary") For j = 1 To 100 n = 1+Round(Rnd * 100) If D.exists(n) Then D(n) = D(n) + 1 Else D.Add n, 1 End If Next j v = D.Items Stop End Sub JP Ronse wrote: Hi Rick, Thanks for your reply. Trying to assist someone over here with some VBA code (generating 1000 random numbers between 1 and 100 and take the top 20), I used the random number as key, and as item the number of times the same random number was generated. My first thought was, if I take each item from the collection and if I should read the key, I know - per key - the number of occurences. Your answer: "... the assumption is that you *know* which Key ..." did me think a bit further. You are completely right herein and I do know the keys: they are between 1 and 100. So I can walk through to get the occurences. A little bit ashamed to make such beginners fault, but thanks for your time and to bring me back on the right track. Wkr, JP "Rick Rothstein" wrote in message ... The idea of assigning a Key is so that you can use it in place of knowing or having to track the position number of your item within the collection (especially since the position number can change with deletions of items ahead of the item), so the assumption is that you *know* which Key you are using to track any particular item. To the best of my knowledge, there is no way to retrieve the Key assigned to an item in code... and, quite frankly, I can't think of a scenario where being able to do so would be needed. Can you explain why you think you would need to do what you have asked (perhaps we can suggest an alternative to you)? -- Rick (MVP - Excel) "JP Ronse" wrote in message ... Hi All, Is there a method to get the used key back from a collection? Suppose you have some (very stupid code) like this: set c = new collection c.add item:=1, key:="A" c.add item:=2, key:="B" c.add item:=3, key:="C" How to know which key was used for c(1), c(2)... TIA. Wkr, JP |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Collections
You can't read the key of a item in a Collection. It is write-only.
You could use a Dictionary instead. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 21 Aug 2009 16:31:35 +0200, "JP Ronse" wrote: Hi Rick, Thanks for your reply. Trying to assist someone over here with some VBA code (generating 1000 random numbers between 1 and 100 and take the top 20), I used the random number as key, and as item the number of times the same random number was generated. My first thought was, if I take each item from the collection and if I should read the key, I know - per key - the number of occurences. Your answer: "... the assumption is that you *know* which Key ..." did me think a bit further. You are completely right herein and I do know the keys: they are between 1 and 100. So I can walk through to get the occurences. A little bit ashamed to make such beginners fault, but thanks for your time and to bring me back on the right track. Wkr, JP "Rick Rothstein" wrote in message ... The idea of assigning a Key is so that you can use it in place of knowing or having to track the position number of your item within the collection (especially since the position number can change with deletions of items ahead of the item), so the assumption is that you *know* which Key you are using to track any particular item. To the best of my knowledge, there is no way to retrieve the Key assigned to an item in code... and, quite frankly, I can't think of a scenario where being able to do so would be needed. Can you explain why you think you would need to do what you have asked (perhaps we can suggest an alternative to you)? -- Rick (MVP - Excel) "JP Ronse" wrote in message ... Hi All, Is there a method to get the used key back from a collection? Suppose you have some (very stupid code) like this: set c = new collection c.add item:=1, key:="A" c.add item:=2, key:="B" c.add item:=3, key:="C" How to know which key was used for c(1), c(2)... TIA. Wkr, JP |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Collections
Hi Dana,
Many thanks for the sample code. (Works perfectly.) This is new to me but is really something I needed since long time. If I may ask, do you know where I can find more about the use of dictionaries? Wkr, JP "Dana DeLouis" wrote in message ... Hi. I'm not sure this is the best method to do this, but here are some general ideas. In these examples, look for the "Keys" and "Items". Sounds like that's what you are looking for. I threw this together quickly, so I hope I didn't make too many mistakes. Sub Demo_Dic() Dim D Dim k Set D = CreateObject("Scripting.Dictionary") D.Add "Athens", 1 'Add some keys - items D.Add "Belgrade", 2 D.Add "Cairo", 3 '// The key for #2 k = D.Keys MsgBox "Second Key is: " & k(2 - 1) End Sub Sub Demo2() Dim D Dim k Dim n Dim j Dim v Set D = CreateObject("Scripting.Dictionary") For j = 1 To 100 n = 1+Round(Rnd * 100) If D.exists(n) Then D(n) = D(n) + 1 Else D.Add n, 1 End If Next j v = D.Items Stop End Sub JP Ronse wrote: Hi Rick, Thanks for your reply. Trying to assist someone over here with some VBA code (generating 1000 random numbers between 1 and 100 and take the top 20), I used the random number as key, and as item the number of times the same random number was generated. My first thought was, if I take each item from the collection and if I should read the key, I know - per key - the number of occurences. Your answer: "... the assumption is that you *know* which Key ..." did me think a bit further. You are completely right herein and I do know the keys: they are between 1 and 100. So I can walk through to get the occurences. A little bit ashamed to make such beginners fault, but thanks for your time and to bring me back on the right track. Wkr, JP "Rick Rothstein" wrote in message ... The idea of assigning a Key is so that you can use it in place of knowing or having to track the position number of your item within the collection (especially since the position number can change with deletions of items ahead of the item), so the assumption is that you *know* which Key you are using to track any particular item. To the best of my knowledge, there is no way to retrieve the Key assigned to an item in code... and, quite frankly, I can't think of a scenario where being able to do so would be needed. Can you explain why you think you would need to do what you have asked (perhaps we can suggest an alternative to you)? -- Rick (MVP - Excel) "JP Ronse" wrote in message ... Hi All, Is there a method to get the used key back from a collection? Suppose you have some (very stupid code) like this: set c = new collection c.add item:=1, key:="A" c.add item:=2, key:="B" c.add item:=3, key:="C" How to know which key was used for c(1), c(2)... TIA. Wkr, JP |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Collections
Hi Chip,
Thanks for the sugeestion but dictionaries are new to me. Do you where I can learn more over this? Wkr, JP "Chip Pearson" wrote in message ... You can't read the key of a item in a Collection. It is write-only. You could use a Dictionary instead. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 21 Aug 2009 16:31:35 +0200, "JP Ronse" wrote: Hi Rick, Thanks for your reply. Trying to assist someone over here with some VBA code (generating 1000 random numbers between 1 and 100 and take the top 20), I used the random number as key, and as item the number of times the same random number was generated. My first thought was, if I take each item from the collection and if I should read the key, I know - per key - the number of occurences. Your answer: "... the assumption is that you *know* which Key ..." did me think a bit further. You are completely right herein and I do know the keys: they are between 1 and 100. So I can walk through to get the occurences. A little bit ashamed to make such beginners fault, but thanks for your time and to bring me back on the right track. Wkr, JP "Rick Rothstein" wrote in message ... The idea of assigning a Key is so that you can use it in place of knowing or having to track the position number of your item within the collection (especially since the position number can change with deletions of items ahead of the item), so the assumption is that you *know* which Key you are using to track any particular item. To the best of my knowledge, there is no way to retrieve the Key assigned to an item in code... and, quite frankly, I can't think of a scenario where being able to do so would be needed. Can you explain why you think you would need to do what you have asked (perhaps we can suggest an alternative to you)? -- Rick (MVP - Excel) "JP Ronse" wrote in message ... Hi All, Is there a method to get the used key back from a collection? Suppose you have some (very stupid code) like this: set c = new collection c.add item:=1, key:="A" c.add item:=2, key:="B" c.add item:=3, key:="C" How to know which key was used for c(1), c(2)... TIA. Wkr, JP |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Collections
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Collections
A Dictionary is similar to a Collection but is more useful and has
more properties. In VBA, create a new module. Then, go to the Tools menu, choose References and check the entry for "Microsoft Scripting Runime". Then, paste in the following code: Dim pDict As Scripting.Dictionary Sub AAA() Dim N As Long Dim KeyVal As String ' create the diction Set pDict = New Scripting.Dictionary ' add some items pDict.Add Key:="a", Item:=1234 pDict.Add Key:="b", Item:=2345 ' list the KEYS of th dictionary For N = 0 To UBound(pDict.Keys) Debug.Print pDict.Keys(N) Next N ' list keys and values For N = 0 To UBound(pDict.Keys) KeyVal = pDict.Keys(N) Debug.Print "Key: " & KeyVal & " Item: " & pDict(KeyVal) Next N ' does item with key exist? KeyVal = "asdf" If pDict.Exists(KeyVal) = True Then Debug.Print "key '" & KeyVal & "' exists, with value" & vbNewLine & _ pDict(KeyVal) Else Debug.Print "Key '" & KeyVal & "' does not exist." End If ' clear out all item from the dictionary pDict.RemoveAll End Sub In this code pDict is the Dictionary object. One of its properties is Keys, which returns an array of all the key values. It also has an Exists method to test whether a key exists in the Dictionary. There are other useful properties and methods. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 21 Aug 2009 17:42:33 +0200, "JP Ronse" wrote: Hi Chip, Thanks for the sugeestion but dictionaries are new to me. Do you where I can learn more over this? Wkr, JP "Chip Pearson" wrote in message .. . You can't read the key of a item in a Collection. It is write-only. You could use a Dictionary instead. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 21 Aug 2009 16:31:35 +0200, "JP Ronse" wrote: Hi Rick, Thanks for your reply. Trying to assist someone over here with some VBA code (generating 1000 random numbers between 1 and 100 and take the top 20), I used the random number as key, and as item the number of times the same random number was generated. My first thought was, if I take each item from the collection and if I should read the key, I know - per key - the number of occurences. Your answer: "... the assumption is that you *know* which Key ..." did me think a bit further. You are completely right herein and I do know the keys: they are between 1 and 100. So I can walk through to get the occurences. A little bit ashamed to make such beginners fault, but thanks for your time and to bring me back on the right track. Wkr, JP "Rick Rothstein" wrote in message ... The idea of assigning a Key is so that you can use it in place of knowing or having to track the position number of your item within the collection (especially since the position number can change with deletions of items ahead of the item), so the assumption is that you *know* which Key you are using to track any particular item. To the best of my knowledge, there is no way to retrieve the Key assigned to an item in code... and, quite frankly, I can't think of a scenario where being able to do so would be needed. Can you explain why you think you would need to do what you have asked (perhaps we can suggest an alternative to you)? -- Rick (MVP - Excel) "JP Ronse" wrote in message ... Hi All, Is there a method to get the used key back from a collection? Suppose you have some (very stupid code) like this: set c = new collection c.add item:=1, key:="A" c.add item:=2, key:="B" c.add item:=3, key:="C" How to know which key was used for c(1), c(2)... TIA. Wkr, JP |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Collections
Hi Bernd,
Thanks for the reply, although I'm Dutch speaking, my German is less then poor (living somewhere in East-Flanders near to the language border) ... Your link brought me to the discussion I had to solve the request about generating rnd numbers. Could it be possible that you pasted the wrong link? I'm really interested to learn more about dictionaries. Wkr, JP "Bernd P" wrote in message ... Hello, See here, please: http://groups.google.de/group/micros...a9edf6ef?hl=de Regards, Bernd |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Collections
Hi Chip,
Many thanks for your reply. I'll go through. My apologies for the typo's in the first reply. (Have read your pages) Wkr, JP "Chip Pearson" wrote in message ... A Dictionary is similar to a Collection but is more useful and has more properties. In VBA, create a new module. Then, go to the Tools menu, choose References and check the entry for "Microsoft Scripting Runime". Then, paste in the following code: Dim pDict As Scripting.Dictionary Sub AAA() Dim N As Long Dim KeyVal As String ' create the diction Set pDict = New Scripting.Dictionary ' add some items pDict.Add Key:="a", Item:=1234 pDict.Add Key:="b", Item:=2345 ' list the KEYS of th dictionary For N = 0 To UBound(pDict.Keys) Debug.Print pDict.Keys(N) Next N ' list keys and values For N = 0 To UBound(pDict.Keys) KeyVal = pDict.Keys(N) Debug.Print "Key: " & KeyVal & " Item: " & pDict(KeyVal) Next N ' does item with key exist? KeyVal = "asdf" If pDict.Exists(KeyVal) = True Then Debug.Print "key '" & KeyVal & "' exists, with value" & vbNewLine & _ pDict(KeyVal) Else Debug.Print "Key '" & KeyVal & "' does not exist." End If ' clear out all item from the dictionary pDict.RemoveAll End Sub In this code pDict is the Dictionary object. One of its properties is Keys, which returns an array of all the key values. It also has an Exists method to test whether a key exists in the Dictionary. There are other useful properties and methods. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 21 Aug 2009 17:42:33 +0200, "JP Ronse" wrote: Hi Chip, Thanks for the sugeestion but dictionaries are new to me. Do you where I can learn more over this? Wkr, JP "Chip Pearson" wrote in message . .. You can't read the key of a item in a Collection. It is write-only. You could use a Dictionary instead. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 21 Aug 2009 16:31:35 +0200, "JP Ronse" wrote: Hi Rick, Thanks for your reply. Trying to assist someone over here with some VBA code (generating 1000 random numbers between 1 and 100 and take the top 20), I used the random number as key, and as item the number of times the same random number was generated. My first thought was, if I take each item from the collection and if I should read the key, I know - per key - the number of occurences. Your answer: "... the assumption is that you *know* which Key ..." did me think a bit further. You are completely right herein and I do know the keys: they are between 1 and 100. So I can walk through to get the occurences. A little bit ashamed to make such beginners fault, but thanks for your time and to bring me back on the right track. Wkr, JP "Rick Rothstein" wrote in message .. . The idea of assigning a Key is so that you can use it in place of knowing or having to track the position number of your item within the collection (especially since the position number can change with deletions of items ahead of the item), so the assumption is that you *know* which Key you are using to track any particular item. To the best of my knowledge, there is no way to retrieve the Key assigned to an item in code... and, quite frankly, I can't think of a scenario where being able to do so would be needed. Can you explain why you think you would need to do what you have asked (perhaps we can suggest an alternative to you)? -- Rick (MVP - Excel) "JP Ronse" wrote in message ... Hi All, Is there a method to get the used key back from a collection? Suppose you have some (very stupid code) like this: set c = new collection c.add item:=1, key:="A" c.add item:=2, key:="B" c.add item:=3, key:="C" How to know which key was used for c(1), c(2)... TIA. Wkr, JP |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Collections
Hello JP,
If you are interested in Dictionaries I suggest to go to http://sulprobil.com/html/listfreq.html and to have a look at my UDF's Pstat and the Xfreq family. There is also an older function List_freq which uses Collections but I have abandoned Collections since. Regards, Bernd |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Collections
Hi Bernd,
TNX, very instructive... Wkr, JP "Bernd P" wrote in message ... Hello JP, If you are interested in Dictionaries I suggest to go to http://sulprobil.com/html/listfreq.html and to have a look at my UDF's Pstat and the Xfreq family. There is also an older function List_freq which uses Collections but I have abandoned Collections since. Regards, Bernd |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Collections
Hello JP,
Thanks for the feedback. Regards, Bernd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A List of Collections and Custom Collections | Excel Programming | |||
For Each and Collections | Excel Programming | |||
Collections of Collections | Excel Programming | |||
Help with collections | Excel Programming | |||
Using Collections | Excel Programming |