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

Try removing the brackets. I'm not sure why you need to do both, why not just a collection or just an array? — SJR
– SJR, Commented Jan 8, 2018 at 15:40
Valid point... is there a collection equivalent for IsInArray? @SJR — SanomaJean
– SanomaJean, Commented Jan 8, 2018 at 15:42
What I mean is that at the end of Test you have a collection of the sheets of interest so you can then work on that. Why do you need anything else? — SJR
– SJR, Commented Jan 8, 2018 at 15:44
@SJR Right, you're right. Is there a way to test if a string is in my collection easily? I only knew how to test if something is in an array. — SanomaJean
– SanomaJean, Commented Jan 8, 2018 at 15:45
OK now I see what you are asking I think. In fact use a Dictionary instead, which has an Exists method (or just stick with an array). — SJR
– SJR, Commented Jan 8, 2018 at 15:45

Mathieu Guindon · Accepted Answer · 2018-01-08 16:03:07Z

13

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.

answered Jan 8, 2018 at 16:03

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vityata Over a year ago

That is actually a pretty good answer, I had to read it twice and to play a bit with code to get exactly what do you mean in the first part. Probabaly you may consider adding an example like this one - github.com/Vitosh/VBA_personal/blob/master/… to make the first part more understandable.

CLR · Accepted Answer · 2018-01-08 16:04:44Z

10

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

edited Jan 8, 2018 at 16:04

answered Jan 8, 2018 at 15:49

CLR

12.3k1 gold badge13 silver badges31 bronze badges

Comments

Vityata · Accepted Answer · 2021-06-23 11:02:44Z

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)

edited Jun 23, 2021 at 11:02

answered Jan 8, 2018 at 16:01

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

5 Comments

Fredrik Over a year ago

Thanks. Just used this code, but the redim should be myCol.count-1. Otherwise you get an empty member in the array which can cause problems (in my case in a RemoveDuplicates).

Vityata Over a year ago

I do not see the reason for the redim myCol.Count-1 This is what I got with the normal code, without -1 - ibb.co/iQfJ38 No empty members...

Fredrik Over a year ago

The argument in the redim is the upper boundary

Grim Over a year ago

The line should be ReDim result(myCol.Count - 1) because otherwise the array has an empty element at the end...

Vityata Over a year ago

@Grim - actually there was some kind of a strange case, that was urging me not to do it that way, but I cannot remember it exactly. Checking the screenshot from my previous comment now, I see it was working ok. Just wondering when it should go wrong... Anyway I have edited it.

Matt · Accepted Answer · 2022-10-02 02:50:18Z

0

Instead of using a standard collection, you can use a dictionary because of the .keys method that can transfer all info to an array. Less coding, simple :)

Dim ws As Worksheet Dim arr

Dim Dic As Scripting.Dictionary Set Dic = New Dictionary

For Each ws In ActiveWorkbook.Worksheets

If ws.Name Like "Template" Then

Dic.Add ws.Name, "Awesome"

End If

Next ws

arr = Dic.Keys

answered Oct 2, 2022 at 2:50

Matt

111 bronze badge

Collectives™ on Stack Overflow

Converting VBA Collection to Array

4 Answers 4

1 Comment

Comments

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related