Restrict type in a Collection inside a class module

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开发者_JAVA技巧 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.

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.)

This is what I did. I liked Rob van Gelder's example, as pointed to by @jtolle, but why should I be content with making a "custom collection class" that will only accept one specific object type (e.g. People), forever? As @jtolle points out, this is super annoying.

Instead, I generalized the idea and made a new class called UniformCollection that can contain any data type -- as long as all items are of the same type in any given instance of UniformCollection.

I added a private Variant that is a placeholder for the data type that a given instance of UniformCollection can contain.

Private mvarPrototype As Variant

After making an instance of UniformCollection and before using it, it must be initialized by specifying which data type it will contain.

Public Sub Initialize(Prototype As Variant)
    If VarType(Prototype) = vbEmpty Or VarType(Prototype) = vbNull Then
        Err.Raise Number:=ERR__CANT_INITIALIZE, _
            Source:=TypeName(Me), _
            Description:=ErrorDescription(ERR__CANT_INITIALIZE) & _
                TypeName(Prototype)
    End If
    ' Clear anything already in collection.
    Set mUniformCollection = New Collection
    If VarType(Prototype) = vbObject Or VarType(Prototype) = vbDataObject Then
        ' It's an object. Need Set.
        Set mvarPrototype = Prototype
    Else
        ' It's not an object.
        mvarPrototype = Prototype
    End If
    ' Collection will now accept only items of same type as Prototype.
End Sub

The Add method will then only accept new items that are of the same type as Prototype (be it an object or a primitive variable... haven't tested with UDTs yet).

Public Sub Add(NewItem As Variant)
    If VarType(mvarPrototype) = vbEmpty Then
        Err.Raise Number:=ERR__NOT_INITIALIZED, _
            Source:=TypeName(Me), _
            Description:=ErrorDescription(ERR__NOT_INITIALIZED)
    ElseIf Not TypeName(NewItem) = TypeName(mvarPrototype) Then
        Err.Raise Number:=ERR__INVALID_TYPE, _
            Source:=TypeName(Me), _
            Description:=ErrorDescription(ERR__INVALID_TYPE) & _
                TypeName(mvarPrototype) & "."
    Else
        ' Object is of correct type. Accept it.
        ' Do nothing.
    End If

    mUniformCollection.Add NewItem

End Sub

The rest is pretty much the same as in the example (plus some error handling). Too bad RvG didn't go the whole way! Even more too bad that Microsoft didn't include this kind of thing as a built-in feature...

I did almost the same code of Jean-François Corbett, but I adapted because for some reason wasn't working.

Option Explicit

Public pParametro As String
Private pColecao As New Collection

Public Sub Inicializar(ByVal parametro As String)
    pParametro = parametro
End Sub

Public Sub Add(NewItem As Object)
If TypeName(NewItem) <> pParametro Then
    MsgBox "Classe do objeto não é compatível à coleção"
Else
    pColecao.Add NewItem
End If
End Sub

Public Property Get Count() As Long
  Count = pColecao.Count
End Property

Public Property Get Item(NameOrNumber As Variant) As Variant
  Set Item = pColecao(NameOrNumber)
End Property

Sub Remove(NameOrNumber As Variant)
  pColecao.Remove NameOrNumber
End Sub

Then, when i want to create an instance from CCollection I make like the code bellow:

    Set pFornecedores = New CCollection
    pFornecedores.Inicializar ("CEmpresa")

Where CEmpresa is the class type from the object I want

Yes. The solution is to make your collection private and then make public wrapper functions to add, remove, getitem and count etc.

It may seem like hassle to write the additional code but it is a more robust solution to encapsulate the collection like this.