Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
collection obj
I'm having some trouble with the collection obj. My first time out.
I seem can't test it. (placing strings into what I would call a hash/dictionary. Am I doing that?) Dim usedFields As Collection Set usedFields = New Collection usedFields.Add("string); ' doesn't die Later, I want to know if an item exists if usedFields.Item("string") if exists.usedFields ... if usedFields.Item("string") < "" if usedFields.Item("string") < 0 All get me the yellow bug line. How do test for exists? Help. Thank you. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
collection obj
On Jan 31, 11:18*am, cate wrote:
I'm having some trouble with the collection obj. *My first time out. I seem can't test it. (placing strings into what I would call a hash/dictionary. *Am I doing that?) Dim usedFields As Collection Set usedFields = New Collection usedFields.Add("string); *' *doesn't die Later, I want to know if an item exists if usedFields.Item("string") if exists.usedFields ... if usedFields.Item("string") < "" if usedFields.Item("string") < 0 All get me the yellow bug line. *How do test for exists? Help. *Thank you. It's one of these - answer myself posts. I installed a reference to Microsoft Scripting Runtime and got a Dictionary. I don't know how this will affect the portability of the code within the company but for now it's working just fine. Sorry for the post. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
collection obj
The thing to keep in mind with collections is they can store multiple
similar items or values but keys are always unique. So be sure to include the optional key, eg Sub test() Dim n As Long Dim col As Collection Set Col = New collection col.Add "abc", "abc" n = 123 col.Add n, CStr(n) Debug.Print ItemExists(col, "abc") ' true Debug.Print ItemExists(col, "xyz") ' false End Sub Function ItemExists(col As Collection, sKey As String) As Boolean Dim v As Variant On Error GoTo errExit v = col(sKey) ItemExists = True Exit Function errExit: End Function I see you have discovered the Dictionary object which indeed adds a lot of functionality. However if the only purpose is to test if items exist I'd stick with the Collection. Regards, Peter T "cate" wrote in message ... I'm having some trouble with the collection obj. My first time out. I seem can't test it. (placing strings into what I would call a hash/dictionary. Am I doing that?) Dim usedFields As Collection Set usedFields = New Collection usedFields.Add("string); ' doesn't die Later, I want to know if an item exists if usedFields.Item("string") if exists.usedFields ... if usedFields.Item("string") < "" if usedFields.Item("string") < 0 All get me the yellow bug line. How do test for exists? Help. Thank you. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
collection obj
Cate,
Good to hear that you were able to answer your own question. As far as portability goes, one thing you can look into is the concept of late binding. You can used late binding to create a Scripting Runtime object with the following code: Dim objDic As Object Set objDic = CreateObject("Scripting.Dictionary") Late binding does not require the Scripting Runtime reference to be checked. So, the code above will behave the same as if you checked the Scripting Runtime reference and used the following code: Dim Dic As Scripting.Dictionary Set Dic = New Scripting.Dictionary (One downside, though, is that late binding does not give you the benefit of Intellisense. However, you could create the code by checking the reference (early binding), writing/running/testing the code in the early bound environment, and then switch the code to late bound environment when you are comfortable with the way that the code is behaving). Best, Matthew Herbert "cate" wrote: On Jan 31, 11:18 am, cate wrote: I'm having some trouble with the collection obj. My first time out. I seem can't test it. (placing strings into what I would call a hash/dictionary. Am I doing that?) Dim usedFields As Collection Set usedFields = New Collection usedFields.Add("string); ' doesn't die Later, I want to know if an item exists if usedFields.Item("string") if exists.usedFields ... if usedFields.Item("string") < "" if usedFields.Item("string") < 0 All get me the yellow bug line. How do test for exists? Help. Thank you. It's one of these - answer myself posts. I installed a reference to Microsoft Scripting Runtime and got a Dictionary. I don't know how this will affect the portability of the code within the company but for now it's working just fine. Sorry for the post. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
collection obj
You could add a key:
Dim usedFields As Collection Dim testStr As String Set usedFields = New Collection usedFields.Add Item:="string", key:=CStr("string") On Error Resume Next testStr = usedFields.Item("string") If Err.Number < 0 Then Err.Clear MsgBox "Doesn't exist" Else MsgBox "it does exist" End If On Error GoTo 0 ========== On the other hand, you could use the dictionary object: Dim UsedFields As Object Set UsedFields = CreateObject("Scripting.Dictionary") UsedFields.CompareMode = 1 UsedFields.Add Key:="string", Item:=0 UsedFields.Add Key:="string2", Item:=1 If UsedFields.Exists("string") Then MsgBox "Yep" Else MsgBox "nope" End If You could set a reference to "Microsoft Scripting Runtime" and use: Dim UsedFields As Scripting.Dictionary Set UsedFields = New Dictionary UsedFields.CompareMode = TextCompare UsedFields.Add Key:="string", Item:=0 UsedFields.Add Key:="string2", Item:=1 If UsedFields.Exists("string") Then MsgBox "Yep" Else MsgBox "nope" End If To set a reference: Inside the VBE Tools|references and check that "microsoft scripting runtime" option. cate wrote: I'm having some trouble with the collection obj. My first time out. I seem can't test it. (placing strings into what I would call a hash/dictionary. Am I doing that?) Dim usedFields As Collection Set usedFields = New Collection usedFields.Add("string); ' doesn't die Later, I want to know if an item exists if usedFields.Item("string") if exists.usedFields ... if usedFields.Item("string") < "" if usedFields.Item("string") < 0 All get me the yellow bug line. How do test for exists? Help. Thank you. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum a collection | Excel Programming | |||
Collection Key | Excel Programming | |||
Collection | Excel Programming | |||
Is a Collection the best option? | Excel Programming |