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.
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:
The final report looks like this:
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.
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 LimitChildren.zip to see the whole thing if you like.
Posting code? Use <pre> tags for VBA and <code> tags for inline.