Excel VBA - creating a collection class within a parent class

Question

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!

Please add more specifics by providing the code snippet you are working on and clearly identifying the problematic part. Best regards, — Alexander Bell
– Alexander Bell, Commented Jan 20, 2015 at 18:59
Private pFields As New Collection and Private pFields As New SampleFields seems ambiguous. Btw, why do you need a Collection class if it's already a built-in object in Excel VBA? — Alexander Bell
– Alexander Bell, Commented Jan 20, 2015 at 19:39
I may need some custom properties/methods of the collection besides the 4 the built-in collection has. — jivko
– jivko, Commented Jan 20, 2015 at 19:50

Rik Sportel · Accepted Answer · 2017-06-07 07:37:56Z

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

cyberponk · Accepted Answer · 2019-05-24 01:15:00Z

-1

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

edited May 24, 2019 at 1:15

answered May 15, 2019 at 23:07

cyberponk

1,88421 silver badges22 bronze badges

Collectives™ on Stack Overflow

Excel VBA - creating a collection class within a parent class

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related