3

I implemented a collection of class objects. I got the instructions for how to create the collection of class objects from an online article, but I don't totally understand it. My collection works. I can add objects to Coll, manipulate their data, and use them from other functions.

The article: http://www.cpearson.com/excel/CollectionClass.aspx

Maybe it is only useful in VBA but not VBA Excel? I get tripped up by that often.

Currently I only add one type of object to the collection, but it would be more useful to add a variety of object types. However, then I wouldn't know what type they were when I looped through each object.

What is the second collection CollKeys for? What are keys? How is it used in Excel VBA? Would it help when adding different types of objects?

' Collection cCRE_Coll

Option Explicit
Private Const msMODULE As String = "cCRE_Coll"

' This is a collection of CRE objects
Private pCount As Long
Private Coll As Collection
Private CollKeys As Collection

  Private Sub Class_Initialize()
        Set Coll = New Collection
        Set CollKeys = New Collection
    End Sub

    Private Sub Class_Terminate()
        Set Coll = Nothing
        Set CollKeys = Nothing
    End Sub

Public Property Get Count() As Long
    pCount = Coll.Count
End Property

Here's how I've tested it, but I need to do a lot more complicated manipulations...

Sub testReadWrite()
    Dim collCRE As New Collection
    Dim collPE As New Collection
    Dim collPP As New Collection
    Dim clsCRE As cCRE
    Dim clsPE As cPE
    Dim clsPP As cPP

    Dim i As Long
    Dim myWS As Worksheet
    Dim lLastRow As Long

    Set myWS = ActiveSheet

    lLastRow = lFindNewRow(myWS) - 1
    ' Item count starts at 1

' Test reading in all the different types of row entries
    For i = giHEADER_ROW + 1 To lLastRow
    If myWS.Cells(i, ColNum.CRE_ID) <> vbNullString Then
        ' Read in a CRE
        Set clsCRE = New cCRE   ' Start with a blank clsCRE
        Call clsCRE.ReadFromWS(myWS, myWS.Cells(i, ColNum.CRE_ID))
        collCRE.Add clsCRE
    ElseIf StrComp(myWS.Cells(i, ColNum.PE_CRE_ID), gsPE_SUMMARY_TAG) = 0 Then
        ' Read in a PE/RCR (not a PP)
        Set clsPE = New cPE
        Call clsPE.ReadFromWS(myWS, myWS.Cells(i, ColNum.PE_ID))
        collPE.Add clsPE
    ElseIf StrComp(myWS.Cells(i, ColNum.PE_ID), gsPP_ID) = 0 Then
        Set clsPP = New cPP
        Call clsPP.ReadFromWS(myWS, i)
        collPP.Add clsPP
    ElseIf myWS.Cells(i, ColNum.PE_ID) = vbNullString Then
        Debug.Print "Cannot Read In Row " & i
    End If
    Next

' In the middle I will change, sort, add and remove objects.

' Test writing out all of the different types of row entries
    For Each clsCRE In collCRE
        Call clsCRE.WriteToWS(ThisWorkbook.Sheets("Sheet1"))
    Next

    For Each clsPE In collPE
        Call clsPE.WriteToWS(ThisWorkbook.Sheets("Sheet1"))
    Next

    For Each clsPP In collPP
        Call clsPP.WriteToWS(ThisWorkbook.Sheets("Sheet1"))
    Next

End Sub
Community
  • 1
  • 1
Shari W
  • 497
  • 3
  • 11
  • 26
  • What is the second collection CollKeys for? Answer as per cpearson link : The CollKeys Collection is used to store the keys of the CFile objects stored in the Coll Collection. We need this second Collection because the Keys of a Collection are write-only -- there is no way to get a list of existing Keys of a Collection. – Santosh Oct 15 '13 at 03:52
  • How is it used in Excel VBA? Answer : As the collection class are defined as `PublicNotCreatable` so we cannot create an instance of the class with the New keyword. This [link](http://support.microsoft.com/kb/555159) will help you how to use the class in vba. – Santosh Oct 15 '13 at 03:57
  • this is a very similar case to [**`this one`**](http://stackoverflow.com/questions/19373081/how-to-use-the-implements-in-excel-vba/19379641#19379641). –  Oct 15 '13 at 11:16
  • and very similar to [**`this one too`**](http://stackoverflow.com/questions/5702362/vba-collection-list-of-keys) –  Oct 15 '13 at 11:22
  • @vba4all No, not really on the first one, but yes on the 2nd one. Why not flag the post and have a moderator confirm it? – GlennFromIowa Oct 19 '14 at 12:34

0 Answers0