VBA Looping through a Collection

Question

I have a collection of files that I selected in the SelectManyFiles function and I want to run multiple private subs on each Drawing in the collection function. Here's my code:

Sub Main()

Dim Drawing As Object
Dim Drawings As Collection
Set Drawings = SelectManyFiles()

For Each Drawing In Drawings
    'Call multiple private subs to run on each drawing
Next Drawing
End Sub

I think there's something wrong with the loop but not sure exactly! Any help is appreciated.

So you do or don't know whether this works? Do you have any other code that you've tried and know doesn't work? — Cᴏʀʏ, Mar 28 '16 at 20:08
This doesn't work. It gives me the error of Object required. — GhostTiger, Mar 28 '16 at 20:14
what's the type of "Drawings"? the "Collection" class is enumerable? — Augusto Goncalves, Mar 28 '16 at 20:28

score 21 · Accepted Answer · answered Mar 28 '16 at 21:06

The collection that's returned by SelectManyFiles is not returning a collection of objects. It's probably returning a collection of Strings, but that's just a guess. Change your sub to this

Sub Main()

Dim Drawing As Variant
Dim Drawings As Collection
Set Drawings = SelectManyFiles()

For Each Drawing In Drawings
    Debug.Print TypeName(Drawing)
Next Drawing
End Sub

And see what the Debug.Print gives you. If it's any scalar (string, long, double, Boolean, etc), then you need to declare Drawing as Variant. Only if all of the collection items are objects can you use Object.

score 2 · Answer 2 · answered Feb 07 '21 at 17:00

2

TRY

    FOR X = 1 TO DRAWING.COUNT
        'STUFF HAPPENS
    NEXT X

answered Feb 07 '21 at 17:00

CMoney

21
1

2

While this code may provide a solution to the question, it's better to add context as to why/how it works. This can help future users learn and eventually apply that knowledge to their own code. You are also likely to have positive-feedback/upvotes from users, when the code is explained. – Amit Verma Feb 07 '21 at 18:07

VBA Looping through a Collection

2 Answers2