Returning a Limited Collection from a Class

I have a custom class, CGroup, which has children of the type CContact (another custom class). I need to loop through all the CContacts for each CGroup and print out those that meet a certain condition – in this example, those with State property of “NE”. Not all CGroups will have a CContact that meets the condition. The CContact class has Name, Company, and State properties. The CGroup class has a Name property and Contacts collection. In addition, it has a read-only ContactsState property that returns a subset of the Contacts collection that have the provided state.

Public Property Get ContactsState(sState As String) As Collection
    Dim i As Long
    Dim colTemp As Collection
    Set colTemp = New Collection
    For i = 1 To mcolContacts.Count
        If mcolContacts.Item(i).State = sState Then
            colTemp.Add mcolContacts.Item(i), mcolContacts.Item(i).Name
        End If
    Next i
    Set ContactsState = colTemp
End Property

The data looks like this:

Excel Immediate Window

The final report looks like this:

Excel Immediate Window

In my main sub, I could loop through all of the CContacts for each CGroup and test the State property. If I do that, I need to know beforehand how many CContacts I have in that CGroup so I can tell it when (or if) to print the dotted line that separates them. I could create a property in CGroup that returns that number, but as long I’m looping through the whole collection, I figured I might as well grab the ones I need and return a collection with only those CContacts. I’m not sure if this really saves any cycles because I’m creating a new collection and adding to it rather than just increment a Long counter. But it does have the added advantage of cleaning up the non-class code by removing all that testing for the State.

Sub ListNE()
    Dim clsGroup As CGroup
    Dim colGroups As Collection
    Dim colConState As Collection
    Dim i As Long, j As Long
    Set colGroups = New Collection
    FillGroups colGroups
    For i = 1 To colGroups.Count
        Set clsGroup = colGroups(i)
        Set colConState = clsGroup.ContactsState(“NE”)
        For j = 1 To colConState.Count
            Debug.Print clsGroup.Name, colConState(j).Name, colConState(j).State
            If j = colConState.Count Then
                Debug.Print String(50, “-“)
            End If
        Next j
    Next i
End Sub

By working with the limited collection, colConState, it’s easy to determine when I’ve reached the last CContact with NE for the State. Since colConState is an empty collection for Group2 (because it has no CContacts with NE), the inner loop never gets run. Even though I don’t show it here, it’s probably more important that I properly destroy all of my class references using this method.

You can download to see the whole thing if you like.

Posted in Uncategorized

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.