Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collection
Hey
How do you test to see if a particular value is in a collection object? For example, I have a collection with 5 items in it. The items in it a "abc", "def", "ghi", "jkl", and "mno". How can I test to see if the value "jkl" is in this collection, and return true or false? Thanks Todd |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collection
Function CollectionContains(TxtValue as String) as Boolean
Dim MyObject as Object CollectionContains = False For each MyObject in ObjectName.CollectionName ' You need to put the actual object and collection name in the code here If MyObject.Value = TxtValue Then CollectionContains = True Next MyObject End Function "Todd Huttenstine" wrote: Hey How do you test to see if a particular value is in a collection object? For example, I have a collection with 5 items in it. The items in it a "abc", "def", "ghi", "jkl", and "mno". How can I test to see if the value "jkl" is in this collection, and return true or false? Thanks Todd |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collection
Todd Huttenstine wrote:
Hey How do you test to see if a particular value is in a collection object? For example, I have a collection with 5 items in it. The items in it a "abc", "def", "ghi", "jkl", and "mno". How can I test to see if the value "jkl" is in this collection, and return true or false? Thanks Todd If you check the reference to Microsoft Scripting Runtime in the VB Editor, you can use a Dictionary Object instead of a Collection and it gets easy. Dim dict As New Dictionary Dim arr, Elem arr = Array("abc", "def", "ghi", "jkl", "mno") For Each Elem In arr dict.Add CStr(Elem), Elem Next Debug.Print dict.Exists("jkl") '<--returns True Debug.Print dict.Exists("xyz") '<--returns False Alan Beban |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collection
I should have mentioned that this assumes that the elements in the array
are unique. Alan Beban Alan Beban wrote: Todd Huttenstine wrote: Hey How do you test to see if a particular value is in a collection object? For example, I have a collection with 5 items in it. The items in it a "abc", "def", "ghi", "jkl", and "mno". How can I test to see if the value "jkl" is in this collection, and return true or false? Thanks Todd If you check the reference to Microsoft Scripting Runtime in the VB Editor, you can use a Dictionary Object instead of a Collection and it gets easy. Dim dict As New Dictionary Dim arr, Elem arr = Array("abc", "def", "ghi", "jkl", "mno") For Each Elem In arr dict.Add CStr(Elem), Elem Next Debug.Print dict.Exists("jkl") '<--returns True Debug.Print dict.Exists("xyz") '<--returns False Alan Beban |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collection
Todd,
Here is a function to test it Public Function IfExists(col As Collection, ByVal sKey As String) On Error GoTo NoSuchKey If VarType(col.Item(sKey)) = vbObject Then ' force an error condition if key does not exist End If IfExists = True Exit Function NoSuchKey: IfExists = False End Function -- HTH RP (remove nothere from the email address if mailing direct) "Todd Huttenstine" wrote in message ... Hey How do you test to see if a particular value is in a collection object? For example, I have a collection with 5 items in it. The items in it a "abc", "def", "ghi", "jkl", and "mno". How can I test to see if the value "jkl" is in this collection, and return true or false? Thanks Todd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help with data collection! | New Users to Excel | |||
Does anybody have a CD collection template | Excel Discussion (Misc queries) | |||
Reset New Collection | Excel Programming | |||
2 Collection questions | Excel Programming | |||
Names Collection | Excel Programming |