vba - Restrict type in a Collection inside a class module

Question

I have a collection inside a class module. I'd like to restrict the object type that is "addable" to this collection, i.e. collection should only ever accept objects of one given type and nothing else.

Is there any way to enforce the type of objects added to a collection?

From what I can tell, there is no built-in way to do this. Is the solution then to make this collection private, and build wrapper functions for the methods usually accessible for Collections, i.e. Add, Remove, Item, and Count?

I kinda hate having to write 3 wrapper functions that add no functionality, just to be able to add some type enforcement to the Add method. But if that's the only way, then that's the only way.

Answer 1

There is no way to avoid wrapper functions. That's just inherent in the "specialization through containment/delegation" model that VBA uses.

You can build a "custom collection class", though. You can even make it iterable with For...Each, but that requires leaving the VBA IDE and editing source files directly.

First, see the "Creating Your Own Collection Classes" section of the old Visual Basic 6.0 Programmer's Guide:

http://msdn.microsoft.com/en-us/library/aa262340(v=VS.60).aspx

There is also an answer here on stackoverflow that describes the same thing:

vb6 equivalent to list<someclass>

However, those are written for VB6, not VBA. In VBA you can't do the "procedure attributes" part in the IDE. You have to export the class module as text and add it in with a text editor. Dick Kusleika's website Daily Dose of Excel (Dick is a regular stackoverflow contributer as you probably know) has a post from Rob van Gelder showing how to do this:

http://www.dailydoseofexcel.com/archives/2010/07/04/custom-collection-class/

In your case, going to all that trouble - each "custom collection" class needs its own module - might not be worth it. (If you only have one use for this and it is buried in another class, you might find that you don't want to expose all of the functionality of Collection anyway.)