How to join a collection in VBA

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).

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 = resultEnd Function

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 iEnd Sub

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

Dim super_col As New CollectionaddColToCol super_col, col1addColToCol super_col, col2