(VBA) What interface does For Each expect? IEnumerable in VBA?

[Rufius]

Hi all,

I am trying to figure out what interface For Each expects. My reason for thinking it is actually looking for one is that the following will work fine:

Dim item As Variant
Dim test As New [COLOR=#ff0000][B]Collection[/B][/COLOR]
For Each item In test
    Exit For
Next item

but this will error:

Dim item As Variant
Dim test As New [COLOR=#ff0000][B]Chart[/B][/COLOR]
For Each item In test
    Exit For
Next item

with the message "Class does not support Automation or does not support expected interface". But what interface?

What I am talking about is basically the rough equivalent of testing for IEnumerable (which the mscorlib objects obviously have in some cases)

 

[starl]

A collection can contain multiple items
A chart is ONE item.
You can instantiate multiple collections (though not the way you have it coded) within a single sub.

I mean.. if you want the detailed explanation of what the difference is.. sorry, but i have no idea where to start... Maybe if you explained why you were trying to do code "New Chart"...
From my point of view - you're comparing apples & oranges.

 

[Rufius]

A collection can contain multiple items
A chart is ONE item.
You can instantiate multiple collections (though not the way you have it coded) within a single sub.

I mean.. if you want the detailed explanation of what the difference is.. sorry, but i have no idea where to start... Maybe if you explained why you were trying to do code "New Chart"...
From my point of view - you're comparing apples & oranges.

Thank you for replying!

Let me ask the question this way: Let's say that I simply want to know whether placement in a For Each for any given variable or object will work. Basically asking the question "is this thing enumerable... can I use it in a For Each".

I could write a function that answers this question by just trying to use For Each and catching the error, but the problem is in the edge case where the collection is zero length, the For Each will not execute, and a false positive will slip through (positive in the sense that the thing will be enumerable).

It seems pretty clear from my tests that there is an interface which For Each is looking for, and I would just like to look for that interface myself.

 

[shg]

Most collections have a Count property.

 

[RickXL]

Hi,

I think the short answer is that you can enumerate the items if it is an object that could contain multiple items.

Instances of such Objects would be Collections, Arrays and Ranges.
Note that a list of individual charts will be automatically maintained in the Charts Collection, as will a list of Worksheets in the Worksheets Collection. If you just need to step through a list of sheets and are not worried whether they are Worksheets or Charts then there is the Sheets Collection that includes both. A list of Shapes will be found in the Shapes Collection.

 

[starl]

If you want to know if you can enumerate an object, you could look it up in the Help. If it says it's a "collection of x" (x being worksheets, charts, shapes, etc) - then you can enumerate it.
Writing code to see if you can enumerate the object... why?