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.

Answer 1

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.

Answer 2

TRY

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