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:
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)
CollectionToArray = result
Public Sub TestMe()
Dim k As Variant
Dim myCol As New Collection
k = CollectionToArray(myCol)
Debug.Print Join(k, ";")
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