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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
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