There is no "in" command or operator. Perhaps you are thinking of the
"Exists" property of a Scripting Dictionary object. You can test whether a
key or item exists in a collection with the following functions. Note that
ItemExists will work only with simple data types (Strings, Longs, etc)
stored in a Collection. It won't work with objects, arrays, or User-Define
Types. The KeyExists function will work with object type variables. You
could modify the ItemExists procedure to work with objects by changing the
line

If Coll(N) = ItemVar Then

to some sort of comparison operation that compares two objects (there is no
built-in, generic way to determine if two objects are "equal" -- the very
definition of "equal" would depend on the type of objects in question).


Function ItemExists(Coll As Collection, ItemVar As Variant) As Boolean

Dim V As Variant
Dim N As Long
On Error Resume Next
For N = 1 To Coll.Count
If Coll(N) = ItemVar Then
ItemExists = True
Exit Function
End If
Next N
ItemExists = False

End Function

Function KeyExists(Coll As Collection, KeyName As String) As Boolean

Dim V As Variant
On Error Resume Next
V = Coll.Item(KeyName)
Select Case Err.Number
Case 0
''''''''''''''''''''''
' Key Exists
''''''''''''''''''''''
KeyExists = True
Case 5
''''''''''''''''''''''
' Key does not exist
''''''''''''''''''''''
KeyExists = False
Case 438
''''''''''''''''''''''
' Item was an object
''''''''''''''''''''''
Err.Clear
Set V = Coll.Item(KeyName)
Select Case Err.Number
Case 0
KeyExists = True
Case Else
KeyExists = False
End Select
Case Else
KeyExists = False
End Select

End Function

Also does a collection have a function like Join?

There is no such function. If you want to add the contents of one collection
to another collection, use code like

Function CombineCollections(CollectionToAddTo As Collection, _
CollectionToAddFrom As Collection)
Dim N As Long
For N = 1 To CollectionToAddFrom.Count
CollectionToAddTo.Add CollectionToAddFrom(N)
Next N
End Function

Note that since there is no way to read a key from a collection, the items
added to CollectionToAddTo will be added without keys. Beyond this, I don't
know what you mean by "Join".

In general, it is better to use Dictionaries that Collections. For more
information and Collection/Dictionary utility procedures, see
http://www.cpearson.com/excel/Collec...ctionaries.htm.



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





"J Streger" wrote in message
...
I was told that you can use the 'in' command to see if a particular element
exists in a collection, but cannot figure out how to do it, and now I'm
skeptical it exists as a function. They described it as:

if strElement in colNames then
...

but this generates a syntax error, and help has no topics i can find. Can
anyone shed light on this? Also does a collection have a function like
Join?
Thanks.

--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

There is no built in Join function for Collections, but you could create one
very easily in VBA:

Function JoinCollection(Coll As Collection, Separator As String) As String
Dim N As Long
Dim S As String
For N = 1 To Coll.Count
If IsObject(Coll(N)) = False Then
S = S & CStr(Coll(N)) & IIf(N < Coll.Count, Separator, "")
End If
Next N
JoinCollection = S
End Function


You would then use this in code like


Sub AAAA()
Dim Coll As New Collection
Dim S As String
Coll.Add "ABC"
Coll.Add "DEF"
Coll.Add "GHI"
S = JoinCollection(Coll, " ")
Debug.Print S
End Sub


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

"J Streger" wrote in message
...
I forogt, When I mean join, I was speaking of the Join function for arrays,
the one that combines all values into a comma separated string. Regardless
I
wrote a function to simulate it, so no worries.

--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003



"Chip Pearson" wrote:

There is no "in" command or operator. Perhaps you are thinking of the
"Exists" property of a Scripting Dictionary object. You can test whether
a
key or item exists in a collection with the following functions. Note
that
ItemExists will work only with simple data types (Strings, Longs, etc)
stored in a Collection. It won't work with objects, arrays, or
User-Define
Types. The KeyExists function will work with object type variables. You
could modify the ItemExists procedure to work with objects by changing
the
line

If Coll(N) = ItemVar Then

to some sort of comparison operation that compares two objects (there is
no
built-in, generic way to determine if two objects are "equal" -- the very
definition of "equal" would depend on the type of objects in question).


Function ItemExists(Coll As Collection, ItemVar As Variant) As Boolean

Dim V As Variant
Dim N As Long
On Error Resume Next
For N = 1 To Coll.Count
If Coll(N) = ItemVar Then
ItemExists = True
Exit Function
End If
Next N
ItemExists = False

End Function

Function KeyExists(Coll As Collection, KeyName As String) As Boolean

Dim V As Variant
On Error Resume Next
V = Coll.Item(KeyName)
Select Case Err.Number
Case 0
''''''''''''''''''''''
' Key Exists
''''''''''''''''''''''
KeyExists = True
Case 5
''''''''''''''''''''''
' Key does not exist
''''''''''''''''''''''
KeyExists = False
Case 438
''''''''''''''''''''''
' Item was an object
''''''''''''''''''''''
Err.Clear
Set V = Coll.Item(KeyName)
Select Case Err.Number
Case 0
KeyExists = True
Case Else
KeyExists = False
End Select
Case Else
KeyExists = False
End Select

End Function

Also does a collection have a function like Join?

There is no such function. If you want to add the contents of one
collection
to another collection, use code like

Function CombineCollections(CollectionToAddTo As Collection, _
CollectionToAddFrom As Collection)
Dim N As Long
For N = 1 To CollectionToAddFrom.Count
CollectionToAddTo.Add CollectionToAddFrom(N)
Next N
End Function

Note that since there is no way to read a key from a collection, the
items
added to CollectionToAddTo will be added without keys. Beyond this, I
don't
know what you mean by "Join".

In general, it is better to use Dictionaries that Collections. For more
information and Collection/Dictionary utility procedures, see
http://www.cpearson.com/excel/Collec...ctionaries.htm.



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





"J Streger" wrote in message
...
I was told that you can use the 'in' command to see if a particular
element
exists in a collection, but cannot figure out how to do it, and now I'm
skeptical it exists as a function. They described it as:

if strElement in colNames then
...

but this generates a syntax error, and help has no topics i can find.
Can
anyone shed light on this? Also does a collection have a function like
Join?
Thanks.

--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003