CODEWITHSUNDEEP
excelvba
jivko
jivko

Reputation: 430

Excel VBA - creating a collection class within a parent class

I've been trying to create a class that is a collection of objects (of another class) and that is within a parent class. I've looked at several questions here but couldn't get it working. So if anyone can post a short code with my parameters, I'd be very grateful.

My parent class is Sample. It should contain a collection SampleFields which should contain objects from the class SampleField. The SampleField objects have only a Name property and it is taken from cells A1 to D1. It should be possible to add and remove items from the SampleFields collection and modify the Name property of the SampleField objects. The SampleFields collection gets its objects upon the initialization of the Sample class.

I need to access it like this - Sample.SampleFields(1).Name

I think it's useless to post my attempt but here it is:

Sub test()

Dim a As New Sample, i As Variant

a.GetFields

For Each i In a.SampleFields
    Debug.Print i.Name
Next

End Sub

Sample class:

Private pFields As New SampleFields

Public Property Get SampleFields() As SampleFields
    Set SampleFields= pFields
End Property

Public Property Set SampleFields(ByVal value As SampleFields)
    Set pFields = value
End Property

Private Sub Initialize_Class()
    Set pFields = New SampleFields
End Sub


Public Sub GetFields()

Dim rngHeaders As Range, rngCell As Range
Set rngHeaders = Range("A1").CurrentRegion.Rows(1)

For Each rngCell In rngHeaders.Cells
    Dim newField As SampleField
    newField.Name = rngCell.Value2
    Me.Fields.AddNewField (newField)   'crashes here with Method or data member not found
Next

End Sub

SampleFields class:

Private pFields As New Collection

Public Sub AddNewField(FieldName As SampleField)
    Me.AddNewField (FieldName)
End Sub

SampleField class:

Private pName As String

Public Property Let Name(value As String)
    pName = value
End Property

Public Property Get Name() As String
    Name = pName
End Property

Thanks!

Upvotes: 1

Views: 1396

Answers (2)

cyberponk
cyberponk

Reputation: 1766

With a little change in Rik's answer, we can use a public collection, eliminating the need of AddField and Get methods:

Class SampleClass:

Public SampleFields As Collection

Private Sub Class_Initialize()
    Set SampleFields = New Collection
End Sub

Then to use it in your module:

Sub Test()
    Dim sField as AnyOtherClass
    Dim sClass as SampleClass

    Set sField = New AnyOtherClass
    Set sClass = New SampleClass

    sField.Name = "SomeName"
    sClass.SampleFields.add sField 'This adds it to the collection

    'Access as per requirement:
    msgbox sClass.SampleFields(1).Name 'Pop-up saying "SomeName"
End Sub

Upvotes: -1

Rik Sportel
Rik Sportel

Reputation: 2679

Very old post, but let me at least answer this: In the sample class, have a Collection. You can forget about the SampleFields class, it's not needed.

Then you only need to have one SampleField class that you pass to this SampleClass method "AddField" that you use to increase the size of the collection.

Sample class should look like this:

Private p_SampleFields as Collection
Private p_SampleField as SampleField

'Initialize this class with it's collection:
Private Sub Class_Initialize()
    Set p_SampleFields = New Collection
End Sub

'Allow for adding SampleFields:
Public Sub AddField(field as SampleField)
    Set p_SampleField = field
    p_sampleFields.add field
End Sub

'Expose the collection:
Public Property Get SampleFields() as Collection
    Set SampleFields = p_SampleFields
End Property

In a regular module you can then use the following:

Sub Test()
    Dim sField as SampleField
    Dim sClass as SampleClass

    Set sField = New SampleField
    Set sClass = New SampleClass

    sField.Name = "SomeName"
    sClass.AddField sField 'This adds it to the collection

    'Access as per requirement:
    msgbox sClass.SampleFields(1).Name 'Pop-up saying "SomeName"
End Sub

Upvotes: 3

Related Questions