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?

you are missing the Enumeration implementation on your collection i.e Items — cyboashu
– cyboashu, Commented Mar 28, 2019 at 16:20
Can you add a minimal reproducible example? Current code examples you have won't compile — David Zemens
– David Zemens, Commented Mar 28, 2019 at 16:25
@DavidZemens Isn't it a bit hard to provide compiling code for VBA? The only thing I see that you need to compile this is an empty class file with the name ItemType. Except obviously the last code snippet which I provided as an example for what I want to get compiling, but doesn't. — Aleksandra Glesaaen
– Aleksandra Glesaaen, Commented Mar 28, 2019 at 16:29

Mathieu Guindon · Accepted Answer · 2019-03-28 17:26:08Z

4

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":

inspection results

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:

Object Browser showing member description

Code Explorer:

Code Explorer showing member description

Rubberduck toolbar:

answered Mar 28, 2019 at 17:26

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Aleksandra Glesaaen Over a year ago

I am a Rubberduck user myself ^^ And the magic comments are something I didn't know about, at least not the ones you mention here. But they seem super useful as having to export, edit, and import again is a real hassle. Would be great if you could document all of your magic comments somewhere, but that should probably be an issue in your github project ^^

Aleksandra Glesaaen Over a year ago

I have seen that issue, but I understand it as you wanting to add this directly into Rubberduck, which is great, but I would be more than content with a simple wiki page telling me what exists :)

cyboashu · Accepted Answer · 2019-03-28 16:22:52Z

3

Add this to your class

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

answered Mar 28, 2019 at 16:22

cyboashu

10.5k2 gold badges32 silver badges48 bronze badges

5 Comments

Nacorid Over a year ago

Note that you need to add the Attribute outside the VBA IDE; export your class module, open with any editor, add the Attribute line (or the whole function), save the file and import it to your Workbook.

Aleksandra Glesaaen Over a year ago

Thanks, this works. Would it be possible to get some sort of explanation? This seems like black magic to me. Does the function name have to be NewEnum or is it the attribute flag that marks it? Would it be possible to name it _NewEnum like the collection to hide it? How is NewEnum defined, i.e. what would I do if I was using an array as a container?

Mathieu Guindon Over a year ago

@JonasGlesaaen arrays are best iterated with a For...Next loop though. For Each iteration over an array is inefficient... doing that would essentially defeat the performance benefits of an object enumerator.

Aleksandra Glesaaen Over a year ago

@MathieuGuindon Thanks for the info, however, it would be instructive to just understand what NewEnum expects if I didn't have the Collections class to use. I.e. how would I implement it myself if I had written a container from scratch.

Mathieu Guindon Over a year ago

@JonasGlesaaen agreed! ...problem is, that's hardly documented at all :(

freeflow · Accepted Answer · 2019-03-29 09:50:27Z

0

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.

answered Mar 29, 2019 at 9:50

freeflow

4,3753 gold badges13 silver badges19 bronze badges

Collectives™ on Stack Overflow

Create a loopable container class in VBA

3 Answers 3

2 Comments

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related