How to join a collection in VBA

Question

Is there a way to join a collection in VBA? I can find join(array, ";"), but this function can not be applied to a collection.

Thanks.

Sporocyst

Take a look at what this guy did - the List.ToString method would do what you want to do here. – Quandary

Answer 1

Unfortunately, no, there's nothing built-in.

You'll have to either

• convert the collection to an array (no built-in for that either, you'll have to loop through all the items) and then use Join(array, ";") or

• join your collection "the hard way" (set first flag, loop through items, add ";" if not first, clear first, add item).

Equivocation

Answer 2

This is how to join it:

Join(CollectionToArray(colData), ",")

And the function:

Public Function CollectionToArray(myCol As Collection) As Variant

    Dim result  As Variant
    Dim cnt     As Long

    ReDim result(myCol.Count - 1)

    For cnt = 0 To myCol.Count - 1
        result(cnt) = myCol(cnt + 1)
    Next cnt

    CollectionToArray = result

End Function

Unequaled

Answer 3

You don't need to use an array.
Just concatenate each element of the collection with chosen delimeter.

Function collectionToString(coll As Collection, delim As String) As String  
    Dim element  
    Dim out As String  
    For Each element In coll  
        out = IIf(out = "", element, out & delim & element)  
    Next  
    collectionToString = out  
End Function  

Paymar

Your answer will be more helpful if also include a little explanation of what the posted code does? Only posting the code may solve the issue, it doesn't help the overall knowledge base – Earreach

It doesn't work correctly when a collection has empty strings as first elements – Disfigure

Answer 4

I need to clarify that the following is NOT the answer to the question above. However, for anyone who arrived here wondering how to merge collections (happened to me), the code below will add the contents of a collection (col2) to another (col1):

Sub addColToCol(col1 As Collection, col2 As Collection)
    Dim i As Integer

    For i = 1 To col2.Count
        col1.Add col2.Item(i)
    Next i
End Sub

If you want to preserve the contents of each collection, declare an additional collection.

Dim super_col As New Collection

addColToCol super_col, col1
addColToCol super_col, col2

Solemnize

This isn't an answer to the OP question (maybe to another question, but not this one) – Ivonne

how does this get a down vote, but the selected answer is just "NO" – Adenoidectomy

Thanks, I hadn't noticed. I was actually looking for how to merge collections and this was the first search result I got, so I thought I'd add the answer for anyone who accidentally stumbled upon this post looking for help. – Solemnize

@Adenoidectomy It would be better to create a new question that this answer would actually be the answer to Jeopardy style. – Calista