Strongly-Typed Collections: The Easy Way

In yesterday's article, I wrote about the benefits of strongly typed collections in VBA.  I also hinted that there was an easier way to create such a collection than following the manual steps:

(1) Make your Class
(2) SAVE
(3) Export the Class
(4) Remove the Class
(5) Manually add the Attribute Lines
(6) Re-Import the edited Class

The challenge with creating strongly-typed collections in VBA is that it requires you to set special class property attributes–namely VB_UserMemID–which cannot be set within the VBA IDE.  In fact, the only way to set these properties is to export the code to a text file, add the Attribute lines, then re-import the code from the text file.

The manual process is confusing for new developers.  I remember thinking the first time I did it, "There's no way this is going to work."  The confusion wears off after you've done it a few times, but it's still an annoying rigamarole.

Luckily, the code you need to create the class is nearly all boilerplate.  And armed with this Notepad++ trick, turning that boilerplate code into a code generation function is a snap.

The Code: BuildStronglyTypedCollectionClass()

As noted in the comments below, you will need a couple of other functions to run this code:

• TempFileName()
• FileWrite()

'This procedure will create a class named {CollectionClassName} that
'   is a strongly typed collection of {ObjectClassName} objects
'   - Inspiration for this class comes from:
'       http://productivebytes.blogspot.com/2012/04/how-to-create-strongly-typed-collection.html
'   - WARNING: calling this procedure will overwrite any existing
'              module named {CollectionClassName}
'Requirements:
'   - TempFileName():
'Usage: BuildStronglyTypedCollectionClass "Person", "People"
'
'   Dim Humans As New People
'   Humans.Add NewPersonObject("John Smith")
'   Humans.Add NewPersonObject("Alice Hall")
'
'   Dim Human As Person
'   For Each Human In Humans
'       Debug.Print Human.FullName
'   Next Human
Sub BuildStronglyTypedCollectionClass( _
    ObjectClassName As String, _
    CollectionClassName As String)
    
    Dim s As String
    s = s & "Attribute VB_GlobalNameSpace = False" & vbNewLine
    s = s & "Attribute VB_Creatable = False" & vbNewLine
    s = s & "Attribute VB_PredeclaredId = False" & vbNewLine
    s = s & "Attribute VB_Exposed = False" & vbNewLine
    s = s & "Option Compare Database" & vbNewLine
    s = s & "Option Explicit" & vbNewLine
    s = s & "" & vbNewLine
    s = s & "'This is a strongly-typed collection of " & ObjectClassName & " objects" & vbNewLine
    s = s & "'  http://productivebytes.blogspot.com/2012/04/how-to-create-strongly-typed-collection.html" & vbNewLine
    s = s & "" & vbNewLine
    s = s & "Private mCol As Collection" & vbNewLine
    s = s & "" & vbNewLine
    s = s & "" & vbNewLine
    s = s & "Private Sub Class_Initialize()" & vbNewLine
    s = s & "    Set mCol = New Collection" & vbNewLine
    s = s & "End Sub" & vbNewLine
    s = s & "" & vbNewLine
    s = s & "Private Sub Class_Terminate()" & vbNewLine
    s = s & "    Set mCol = Nothing" & vbNewLine
    s = s & "End Sub" & vbNewLine
    s = s & "" & vbNewLine
    s = s & "Property Get Item(Index As Variant) As " & ObjectClassName & "" & vbNewLine
    s = s & "Attribute Item.VB_UserMemId = 0" & vbNewLine
    s = s & "    'There is an attribute above this comment (hidden in the VBA IDE)" & vbNewLine
    s = s & "    '   that makes this property the default property for the class" & vbNewLine
    s = s & "    Set Item = mCol.Item(Index)" & vbNewLine
    s = s & "End Property" & vbNewLine
    s = s & "" & vbNewLine
    s = s & "Property Get NewEnum() As IUnknown" & vbNewLine
    s = s & "Attribute NewEnum.VB_UserMemId = -4" & vbNewLine
    s = s & "    'There is an attribute above this comment (hidden in the VBA IDE)" & vbNewLine
    s = s & "    '   that makes this property the default enumerator property for the class" & vbNewLine
    s = s & "    Set NewEnum = mCol.[_NewEnum]" & vbNewLine
    s = s & "End Property" & vbNewLine
    s = s & "" & vbNewLine
    s = s & "Public Sub Add(Item As " & ObjectClassName & ", Optional Key As Variant)" & vbNewLine
    s = s & "    mCol.Add Item, Key" & vbNewLine
    s = s & "End Sub" & vbNewLine
    s = s & "Public Function Count() As Long" & vbNewLine
    s = s & "    Count = mCol.Count" & vbNewLine
    s = s & "End Function" & vbNewLine
    s = s & "Public Sub Remove(Index As Variant)" & vbNewLine
    s = s & "    mCol.Remove Index" & vbNewLine
    s = s & "End Sub"
    
    
    'Save the generated code to a temporary file
    Dim fpTemp As String
    fpTemp = TempFileName()  'https://nolongerset.com/tempfilename/
    FileWrite fpTemp, s      'https://nolongerset.com/text-files-read-write-append/
    
    'Load the file
    ' NOTE: this overwrites any existing {CollectionClassName}
    '       class module with no warning
    LoadFromText acModule, CollectionClassName, fpTemp
    
    'Clean up the temporary file
    Kill fpTemp
    
End Sub

Sample usage

Here's an example of the function in action using the oVehicle class from yesterday's article.

BuildStronglyTypedCollectionClass "oVehicle", "collVehicles"

And here is the collVehicles class that the above code generates:

Option Compare Database
Option Explicit

'This is a strongly-typed collection of oVehicle objects
'  http://productivebytes.blogspot.com/2012/04/how-to-create-strongly-typed-collection.html

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 oVehicle
    'There is an attribute above this comment (hidden in the VBA IDE)
    '   that makes this property the default property for the class
    Set Item = mCol.Item(Index)
End Property

Property Get NewEnum() As IUnknown
    'There is an attribute above this comment (hidden in the VBA IDE)
    '   that makes this property the default enumerator property for the class
    Set NewEnum = mCol.[_NewEnum]
End Property

Public Sub Add(Item As oVehicle, Optional Key As Variant)
    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

Here are the hidden attributes that you only see if you export the class:

Image by domaxi198 from Pixabay