CODEWITHSUNDEEP
classvbacollectionsexcel
rkane
rkane

Reputation: 33

subobjects of class module not working in vba for excel

I've searched for a bit, but I couldn't find a similar enough question/answer. So here it goes:

I have a class object called Project. A Project can have multiple Scenarios assoicated with it.

I've created the class modules for each object. But I am having difficulty in, I believe, instantiating the Scenarios collection for a given Project.

Here are the class modules:

1) cProject:

Private pProjectID As Integer 
Private pName As String 
Private pDateCreated As String 
Private pScenarios As cScenarios

' PROPERTIES

Public Property Get ProjectID() As Integer
    ProjectID = pProjectID 
End Property
Public Property Let ProjectID(value As Integer)
    pProjectID = value 
End Property
Public Property Get name() As String
    name = pName 
End Property 
Public Property Let name(value As String)
    pName = value 
End Property 
Public Property Get Scenarios() As cScenarios
    Set Scenarios = pScenarios 
End Property
Public Property Set Scenarios(value As cScenarios)
    Set pScenarios = value 
End Property

2) cScenarios collection class module:

Private pScenarios As Collection

Private Sub Class_Initialize()
    Set pScenarios = New Collection
End Sub
Private Sub Class_Terminate()
    Set pScenarios = Nothing
End Sub
Public Function Item(index As Variant) As cScenario
    Set Item = pScenarios.Item(index)
End Function
Public Property Get Count() As Long
    Count = pScenarios.Count
End Property
Public Sub Add(obj As cScenario)
    pScenarios.Add obj
End Sub
Public Sub Remove(index As Variant)
    pScenarios.Remove index
End Sub

And finally (3) the Scenario class object:

Private pScenarioID As Integer
Private pName As String
Private pDateCreated As String
Private pParent As cProject

Public Property Get ScenarioID() As Integer
    ScenarioID = pScenarioID
End Property
Public Property Let ScenarioID(value As Integer)
    pScenarioID = value
End Property
Public Property Get name() As String
    name = pName
End Property
Public Property Let name(value As String)
    pName = value
End Property
Public Property Get parent() As cProject
    parent = pParent
End Property
Public Property Let parent(value As cProject)
    pParent = value
End Property

Here is a standard module:

Sub test1()

Dim cS As cScenarios
Dim s As cScenario

Set cS = New cScenarios

For i = 1 To 3
    Set s = New cScenario
    s.name = "s" & i
    cS.Add s
Next

Debug.Print cS.Item(3).name
Debug.Print cS.Count

End Sub

This works. All is good. For now. I am able to populate cS with multiple scenarios. However, if I reference the scenarios collection as a child object of the project (see below in test2() ), I get a "Run-time error '91': Object variable or With block variable not set" triggered on the cs.Add call.

Sub test2()

Dim p As cProject
Dim cS As cScenarios
Dim s As cScenario

Set p = New cProject
Set cS = p.Scenarios

For i = 1 To 3
    Set s = New cScenario
    s.name = "s" & i
    cS.Add s
Next

Debug.Print cS.Item(3).name
Debug.Print cS.Count

End Sub

What did I do wrong building my class modules and/or how do I fix it? Thanks.

Upvotes: 3

Views: 2355

Answers (1)

ssarabando
ssarabando

Reputation: 3517

You are not initializing pScenarios in the cProject class before trying to access it with Add(). You can fix that by adding an initializer to cProject:

Private Sub Class_Initialize()
    Set pScenarios = New cScenarios
End Sub

This will guarantee that the cS instance will not be Nothing when you try to invoke Add on it inside test2. Another way (weaker IMO) would be to set p.Scenarios = new cScenarios after newing up p inside test2. Also, make sure that the property setter for cScenario.parent is Property Set instead of Property Let.

Upvotes: 1

Related Questions