Join VBA collection items into a string

CollectionToString.bas

Function CollectionToString(C As Collection, Optional Delimiter As String, Optional TrailingDelimiter As Boolean) As String
' Join a collection into a single string
' OPTIONAL: choose delimiter and specify whether to include a trailing delimiter (e.g. "a;b;c" vs "a;b;c;", where ";" is the delimiter)

    ' Transfer collection to array
    CollectionToString = Join(CollectionToArray(C, 0, C.Count + IIf(TrailingDelimiter, 1, 0)), Delimiter) ' See Referenced Gists below

End Function

Function CollectionToArray(C As Collection, Optional StartIdx As Long, Optional Size As Long) As Variant
' Convert a collection into an array

Dim A() As Variant
Dim Ci As Variant   ' Collection element
Dim i As Long
    
    ' Make sure Size is not less than collection size
    If Size < C.Count Then Size = C.Count
    
    ' Transfer collection to array
    ReDim A(StartIdx To StartIdx + Size - 1)
    For Each Ci In C
        A(i) = Ci
        i = i + 1
    Next

    CollectionToArray = A

End Function

CollectionToStringTest.bas

Sub CollectionToStringTest()

Dim C As Collection: Set C = New Collection

    C.Add "a"
    C.Add "b"
    C.Add "c"

    Debug.Print "No delimiter: " & CollectionToString(C)                    ' No delimiter: abc
    Debug.Print "No trailing delimiter: " & CollectionToString(C, ",")      ' No trailing delimiter: a,b,c
    Debug.Print "Trailing delimiter: " & CollectionToString(C, ",", True)   ' Trailing delimiter: a,b,c,

End Sub

Referenced_Gists.md

CollectionToArray

Hey There, I used this for an automation, and it worked great! Thanks for making this available. I did notice that when if I ran the macro more than once the contents of A() were preserved, so the list grew and grew with each run. I was able to prevent this by adding Erase A() after the variable declarations in CollectionToArray. I wanted to note this incase anyone else needed the same behavior. Thanks again!

Great catch! Feel free to edit this gist with your fix if you like.

Glad this has been helpful for you otherwise.

Thank you for the very helpful functionality