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