VBA – Join a Collection (Collection to Array)

Working with VBA is sometimes trivial – you need to rewrite and reinvent the wheel every now and then 🙂 I mean, defining your own sorting method, your own test driven developement framework, etc.

But, this is the funny & interesting part 🙂 Even if you are working with the Collection object and you expect that there should be a quick way to present it as a String, something like Join(collection, deliminator) you are not sure whether you are disappointed that a method like this does not exist and you have to write it yourself. Because Join(collection, deliminator) actually takes an Array and not Collection as an input.

The best way to write a method, that is already written in the VBEditor for some other data type is simply to convert your data type to the data type which has a method, written for it and to work from there. Thus, in my case, I had to convert Collection to Array, to be able to use the nice method of the arrays Join().

This is what I came up with:

Option Explicit

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

Public Sub TestMe()

    Dim k       As Variant
    Dim myCol   As New Collection

    myCol.Add 1
    myCol.Add 2
    myCol.Add 3

    k = CollectionToArray(myCol)
    Debug.Print Join(k, ";")
    
End Sub

As trivial as possible but it would save me (and probably you) about 15 minutes:

7 minutes researching how to Join a Collection
3 minutes wondering why are you still coding with VBA
3 writing the function
2 testing it

Yup. That’s it – also in GitHub.

VBA – Join a Collection (Collection to Array)

Related posts: