How to Create a Strongly typed Collection in Vba

An Excel File with the sample code can be found here

In this blog post I will show you how to create a Strongly Type Collection Class wich has both an Item as default property and for wich we can use the For Each Loop.
First we create a very simple Class named Person with just three properties Name, Surname and Date of Birth

Option Explicit

Dim mName As String
Dim mSurname As String
Dim mDateofBirth As Date

Public Property Get Name() As String
  Name = mName
End Property
Public Property Let Name(strName As String)
   mName = strName
End Property
Public Property Get Surname() As String
   Surname = mSurname
End Property
Public Property Let Surname(strSurname As String)
   mSurname = strSurname
End Property
Public Property Get DateOfBirth() As Date
  DateOfBirth = mDateofBirth
End Property
Public Property Let DateOfBirth(dteDateofBirth As Date)
   mDateofBirth = dteDateofBirth
End Property 

Then we can create the Collection Class called People

Option Explicit

'This is going to be a stroingly type Collection

Private mCol As Collection

Private Sub Class_Initialize()
  Set mCol = New Collection
End Sub
Private Sub Class_Terminate()
  Set mCol = Nothing
End Sub
Property Get Item(Index As Variant) As Person
   'Attribute Item.VB_UserMemId = 0
   'This Attribute makes Item the default property
      
   Set Item = mCol.Item(Index)
End Property
Property Get NewEnum() As IUnknown
  'Attribute NewEnum.VB_UserMemId = -4
  'Attribute NewEnum.VB_MemberFlags = "40"
  
  'The first Attribute makes it the Default Enumerator Property
  'The second Attribute makes the Enumerator a hidden property. This does not work with the VBA intellisense
 
  
  'This Routine  Get the Enumerator for the Collection.
  'To get this to work you must add two attributes
  
  
  
  Set NewEnum = mCol.[_NewEnum]
End Property
Public Sub Add(Item As Person, Optional key As Variant)
  Call mCol.Add(Item, key)
End Sub
Public Function Count() As Long
   Count = mCol.Count
End Function
Public Sub Remove(Index As Variant)
   mCol.Remove (Index)
End Sub

There are few Attributes that you cannot see in the VBA IDE. If you export a file from one of your VB6 procedures and view it, you'll notice that some Attributes, not visible while editing your code, are added to the top of the routine(s). The two properties in question will look something like this:

Property Get Item(Index As Variant) As Parameter
       Attribute Item.VB_UserMemId = 0   

       Set Item = m_Collection.Item(Index)
End Property

Property Get NewEnum() As IUnknown

   Attribute NewEnum.VB_UserMemId = -4  
   Attribute NewEnum.VB_MemberFlags = "40"

  Set NewEnum = Me.mCollection.[_NewEnum]

End Property

Note that the Attribute directive must be just below the functions signatures, otherwise the code will not work.
Now the above all looks "normal" except for the addition of the three "Attribute" Lines.
In the Item Property the line "Attribute Item.VB_UserMemId = 0" makes it the default property.
In the NewEnum, the "Attribute NewEnum.VB_UserMemId = -4" makes it the Default Enumeration Property (I'm sure you recognize the "-4" part.)
The Attribute NewEnum.VB_MemberFlags = "40" is to make the Enumerator a Hidden property, but, technically, this is not recognized in VBA, so it will be visible in IntelliSense, but I don't find that a big deal.

The solution is to
(1) Make your Class
(2) SAVE
(3) Export the Class
(4) Remove the Class (steps 3 and 4 can be combined into one, as it asks you if you wish to "Export" when you right-click and choose "Remove")
 (5) Manually add the Attribute Lines as shown above
 (6) Re-Import the edited Class

 An easier way, as one of my reader pointed out is
1) To write the Attributes directives directly on the VBA Ide. You will get a syntax error. Ignore it
2)  Export the Class
3) Remove the Class
4) Reinport it again.
 The Attribute will be in the .cls file, but they will not be visible (you can add the Attribute NewEnum.VB_MemberFlags = "40" line if you wish -- it won't hurt anything -- but it won't be recognized in VBA, it will just be quietly ignored. So there's no reason to bother doing this, really.)
As you know, editing the code thereafter has some propensity to lose these properties (even in VB6) and so this may have to be repeated occassionally. (A bit of a pain.) The alternative is to create your class 100% within VB6 and then import it into your VBA Project. Or, even better, make it in VB6, debugg it, get it running 100%, compile to DLL and then add this DLL to your references. This last concept is probably the most solid, but there could be deployment issues as your DLL now has to be correctly Registered on the Client machine. Not that this is a big problem, but it's not as easy as distributing a VBA Project...

This is the Code you can use to test the Class

Option Explicit

Sub prova()

Dim Employee As Person
Dim Director As Person
Dim Team As People
Dim p As Person
Dim i As LongSet Employee = New Person
Employee.DateOfBirth = "10 Jan 1974"
Employee.Name = "Mario"
Employee.Surname = "Rossi"Set Director = New Person
Director.DateOfBirth = "10 Mar 1970"
Director.Name = "Giulia"
Director.Surname = "Verdi"Set Team = New People
Call Team.Add(Employee)
Call Team.Add(Director)

For i = 1 To Team.Count
 Debug.Print Team(i).Name
  
Next i

For Each p In Team
  Debug.Print p.Name
NextEnd Sub