VBA Looping through a Collection

vba loops collections autocad
GhostTiger picture GhostTiger · Mar 28, 2016 · Viewed 42.3k times · Source

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

Dick Kusleika picture Dick Kusleika · Mar 28, 2016

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.

Related questions

Loop through each row of a range in Excel

This is one of those things that I'm sure there's a built-in function for (and I may well have been told it in the past), but I'm scratching my head to remember it. How do I loop through each row …

Read More
VBA - how to conditionally skip a for loop iteration

I have a for loop over an array. What I want to do is test for a certain condition in the loop and skip to the next iteration if true: For i = LBound(Schedule, 1) To UBound(Schedule, 1) If (Schedule(i, 1) &…

Read More
Continue For loop

I have the following code For x = LBound(arr) To UBound(arr) sname = arr(x) If instr(sname, "Configuration item") Then '**(here i want to go to next x in loop and not complete the code below)** '// other …

Read More