> ## Content Index
> Fetch the complete content index at: https://nolongerset.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# Strongly-Typed Collections: The Easy Way
- URL: https://nolongerset.com/strongly-typed-collection-classes-the-easy-way/
- Published: 2021-03-28T02:31:35.000Z
- Updated: 2026-05-08T13:05:41.000Z
- Description: Creating a strongly-typed collection class in VBA requires jumping through code export-import hoops. What if you didn't have to, though?
- Author: Mike Wolfe
- Tags: Code Library, #DesignProcedures, Advanced, #CodeGeneration, #Import 2026-05-20 02:59

In [yesterday's article](https://nolongerset.com/strongly-typed-collections-in-vba/), 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](http://productivebytes.blogspot.com/2012/04/how-to-create-strongly-typed-collection.html):

> (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](https://nolongerset.com/vba-code-generation-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](https://nolongerset.com/tempfilename/)()
- [FileWrite](https://nolongerset.com/text-files-read-write-append/)()

```vba
'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](https://nolongerset.com/strongly-typed-collections-in-vba/).

```vba
BuildStronglyTypedCollectionClass "oVehicle", "collVehicles"
```

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

```vba
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:

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2021/03/image-30.png)

*Image by [domaxi198](https://pixabay.com/users/domaxi198-10651890/?utm%5Fsource=link-attribution&utm%5Fmedium=referral&utm%5Fcampaign=image&utm%5Fcontent=3821715) from [Pixabay](https://pixabay.com/?utm%5Fsource=link-attribution&utm%5Fmedium=referral&utm%5Fcampaign=image&utm%5Fcontent=3821715)*