CODEWITHSUNDEEP
vbaexcel
KyawLay
KyawLay

Reputation: 403

How to make object collections as other object's Property in Excel VBA

I have no idea how to achieve following code snippet in excel vba. What I want to do is to assign Subject Collections for User object from u.assignSubjects method.
[Class Subject] 

Private sName As String
Public Property Get Name() As String
    Name = sName
End Property
Public Property Let Name(n As String)
    sName = n
End Property

[Class User] 

Private sName As String
Private sSubjects As Collection

Public Property Get Name() As String
    Name = sName
End Property
Public Property Let Name(n As String)
    sName = n
End Property
Public Property Get Subjects() As Collection
    Subjects = sSubjects
End Property
Public Property Let Subjects(s As Collection)
    sSubjects = s
End Property
Public Sub assignSubjects()
    Dim s1 As clsSubject
    Dim s2 As clsSubject
    Set s1 = New clsSubject
    Set s2 = New clsSubject

    s1.Name = "English"
    s2.Name = "Math"

    Set sSubjects = New Collection
    sSubjects.Add s1, "subject1"

    Set sSubjects = New Collection
    sSubjects.Add s2, "subject2"
End Sub

[Implementation]

Sub run()
    Dim u As clsUser
    Set u = New clsUser
    u.assignSubjects
    For Each a In u.Subjects
        Debug.Print u.Subjects(a).Name
    Next
End Sub

Please Help me

Upvotes: 0

Views: 48

Answers (1)

Axel Richter
Axel Richter

Reputation: 61860

Since your sSubjects is a Collection object, you must use Set to assign something to it.

Furthermore you should not create a New Collection more than once. Instead you could do that with Private Sub Class_Initialize().

clsSubject

Private sName As String

Public Property Get Name() As String
    Name = sName
End Property
Public Property Let Name(n As String)
    sName = n
End Property

clsUser

Private sName As String
Private sSubjects As Collection

Public Property Get Name() As String
    Name = sName
End Property
Public Property Let Name(n As String)
    sName = n
End Property
Public Property Get Subjects() As Collection
    Set Subjects = sSubjects
End Property
Public Property Set Subjects(s As Collection)
    Set sSubjects = s
End Property
Private Sub Class_Initialize()
    Set Me.Subjects = New Collection
End Sub
Public Sub assignSubjects()
    Dim s1 As clsSubject
    Dim s2 As clsSubject
    Set s1 = New clsSubject
    Set s2 = New clsSubject

    s1.Name = "English"
    s2.Name = "Math"

    Me.Subjects.Add s1, "subject1"

    Me.Subjects.Add s2, "subject2"
End Sub

Module

Sub run()
    Dim u As clsUser
    Set u = New clsUser
    u.assignSubjects

    Dim s As clsSubject
    Set s = New clsSubject
    s.Name = "German"

    u.Subjects.Add s

    For Each a In u.Subjects
        Debug.Print a.Name
    Next
End Sub

Upvotes: 2

Related Questions