Converting VBA Collection to Array

Question

I am trying to create an array with all the worksheet names in my workbook that have the word 'Template' in it. I thought the easiest way to do this would be to create a collection first and then convert it to an array but I am having trouble. Right now the error I am getting is on the

collectionToArray (col)

line. I am receiving an

Argument Not Optional error

Pretty stuck, any help is super appreciated. Thanks!!

Public col As New Collection
Public Sub Test()
For Each ws In ThisWorkbook.Worksheets
    If InStr(ws.Name, "Template") <> 0 Then
        col.Add ws.Name
    End If
Next ws

collectionToArray (col)
End Sub

Function collectionToArray(c As Collection) As Variant()
    Dim a() As Variant: ReDim a(0 To c.Count - 1)
    Dim i As Integer
    For i = 1 To c.Count
        a(i - 1) = c.Item(i)
    Next
    collectionToArray = a
End Function

Answer 1

collectionToArray (col)

Notice that whitespace between the function's name and its argument list? That's the VBE telling you this:

I'll take that argument, evaluate it as a value, then pass it ByVal to that procedure you're calling, even if the signature for that procedure says ByRef, explicitly or not.

This "extraneous parentheses" habit is inevitably going to make you bump into weird "Object Required" runtime errors at one point or another: lose it.

The Function is overdoing it IMO: a Variant can perfectly well wrap an array, so I'd change its signature to return a Variant instead of a Variant().

Integer being a 16-bit signed integer type (i.e. short in some other languages), it's probably a better idea to use a Long instead (32-bit signed integer, i.e. int in some other languages) - that way you'll avoid running into "Overflow" issues when you need to deal with more than 32,767 values (especially common if a worksheet is involved).

Public col As New Collection

This makes col an auto-instantiated object variable, and it has potentially surprising side-effects. Consider this code:

Dim c As New Collection
c.Add 42
Set c = Nothing
c.Add 42
Debug.Print c.Count

What do you expect this code to do? If you thought "error 91, because the object reference is Nothing", you've been bitten by auto-instantiation. Best avoid it, and keep declaration and assignments as separate instructions.

Other than that, CLR's answer has your solution: a Function should return a value, that the calling code should consume.

result = MyFunction(args)

You'll notice the VBE clearing any whitespace you might be tempted to add between MyFunction and (args) here: that's the VBE telling you this:

I'll take that argument, pass it to MyFunction, and assign the function's return value to result.

Answer 2

It's all there, you're just not using the Function as a function. You need to store the result in something, like 'NewArray'..?

Public col As New Collection
Public Sub Test()
For Each ws In ThisWorkbook.Worksheets
    If InStr(ws.Name, "Template") <> 0 Then
        col.Add ws.Name
    End If
Next ws


' Tweaked as per Vityata's comment

If col.Count > 0 Then 
    newarray = collectionToArray(col)
Else
    ' Do something else
End If

End Sub

Function collectionToArray(c As Collection) As Variant()
    Dim a() As Variant: ReDim a(0 To c.Count - 1)
    Dim i As Integer
    For i = 1 To c.Count
        a(i - 1) = c.Item(i)
    Next
    collectionToArray = a
End Function

Answer 3

This is my collectionToArray function:

Public Function CollectionToArray(myCol As Collection) As Variant

    Dim result  As Variant
    Dim cnt     As Long
    
    If myCol.Count = 0 Then
        CollectionToArray = Array()
        Exit Function
    End If
    
    ReDim result(myCol.Count - 1)
    For cnt = 0 To myCol.Count - 1
        result(cnt) = myCol(cnt + 1)
    Next cnt
    CollectionToArray = result

End Function

It is better than the one you are using, because it will not give an error, if the collection is empty. To avoid the error on an empty collection in your case, you may consider adding a check like this:

If col.Count > 0 Then k = CollectionToArray(col)