Create a loopable container class in VBA

Question

I have been trying to clean up my code a bit and make it more similar to the Excel object model, and I was wondering if it is possible to create a "loopable" container class in VBA, e.g. similar to how you can do:

Dim Sheet As Worksheet
For Each Sheet In ThisWorkbook.Worksheets
   ' ...
Next Sheet

I want this functionality for my own container.

Say I create my own class called Container which contains items of some class ItemType (this can simply be an empty class for this example):

' Class Container
' The container contains items of a class I will call ItemType

Private Type MContainer
  Items As Collection ' Could also be implemented in terms of an array
End Type

Private This As MContainer

Public Property Get Item(ByVal Index As Long) As ItemType
Attribute Item.VB_UserMemId = 0 'Makes it so I can access elements like a Collection
  Set Item = This.Items(Index)
End Property

Public Function Add() As ItemType
  This.Items.Add
  Set Add = This.Items(This.Items.Count)
End Function

Private Sub Class_Initialize()
  Set This.Items = New Collection
End Sub

I then want to loop through the items in my container with the For Each..., but this doesn't work. See the following example for how I ideally want it to work:

Public Sub MyMethod()

  Dim Stuff As New Container
  Stuff.Add

  Dim Element As ItemType
  For Each Element In Stuff ' <- This will not work
    ' Do something
  Next Element

End Sub

The final For loop is what I am looking at making work. Is this possible? Basically the issue is that I can't call For Each on my Container class similar to how you can with e.g. the Excel.Sheets class. Is this possible to achieve in VBA?

Answer 1

For Each iteration requires a special member attribute value to work, and a NewEnum property or function returning an IUnknown.

Every collection class that can be iterated with a For Each loop has a hidden [_NewEnum] member (the square brackets are required for accessing the hidden member, since the underscore prefix is illegal for an identifier in VBA.

Tweaking module and member attributes isn't possible to do directly in the VBE, so you need to remove/export the module, modify it in e.g. Notepad++, save the changes, then re-import it into your project.

Or, have Rubberduck (disclaimer: I contribute to this open-source project) do it for you, using annotations (aka "magic comments"):

'@Enumerator
'@Description("Gets an enumerator that iterates through the internal object collection.")
Public Property Get NewEnum() As IUnknown
    Set NewEnum = this.Items.[_NewEnum]
End Function

'@DefaultMember
'@Description("Gets/sets the element at the specified index.")
Public Property Get Item(ByVal index As Long) As ItemType
    Set Item = this.Items(index)
End Property

Then parse the project (Ctrl+`) and bring up the Inspection Results toolwindow (Ctrl+Shift+i) - there should be a number of "Missing Attribute" results under "Rubberduck Opportunities":

Click "Fix all occurrences in module" in the bottom pane, to synchronize the hidden attributes with the annotation comments.

If you have "Missing Annotation" results, Rubberduck has determined that a module/member has a non-default value for a given attribute, and is able to similarly add an annotation comment that surfaces/documents it with a comment.

The Code Explorer (Ctrl+R), the Rubberduck toolbar, and the VBE's own Object Browser (F2) will display the contents of the VB_Description attribute, so @Description annotations are particularly useful to have on any public procedure.

Object Browser:

Code Explorer:

Rubberduck toolbar:

Answer 2

Add this to your class

Public Function NewEnum() As IUnknown
Attribute NewEnum.VB_UserMemId = -4
    Set NewEnum = Items .[_NewEnum]
End Function

Answer 3

An alternative approach to this issue is not to use a Collection but a Scripting.Dictionary. One of the advantages of a scripting dictionary is that it can return arrays of the keys and items of the dictionary. Iterating over an array in VBA is a trivial exercise.