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 cannot be applied to the collection.

Thanks.

Answer 1

Unfortunately, no, there is nothing built-in there.

You have to either

• convert the collection to an array (there are also no built-in functions for this, you will have to iterate over all the elements ), and then use Join(array, ";") or

• join your collection in the "hard way" (set the first flag, go through the elements, add ";", if not first , clear first , add the element).

Answer 2

Here's how to join it:

 Join(CollectionToArray(colData), ",") 

And 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 

Answer 3

I need to clarify that the following is NOT the answer to the question above. However, for those who came here thinking about how to combine the collections (happened to me), the code below will add the contents of the 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 keep the contents of each collection, declare an additional collection.

 Dim super_col As New Collection addColToCol super_col, col1 addColToCol super_col, col2 

Answer 4

Good afternoon,

I think I have a Beaucoup plus Rapide method:

 Set dict = CreateObject("Scripting.Dictionary") Set dict = myCol a = Join(dict.items, ",") 

Eric