Nested VBA collection of classes returning "Argument not optional" error

Question

I am trying to represent the idea of nested classes with collections of child classes with the natural example of Grandmother - Married Daughters - Little kids case, so I have 3 classes as following:

' Class GrandMother:

Private pMarriedDaughter As Collection    
Public Property Get MarriedDaughter() As Collection
 MarriedDaughter = pMarriedDaughter
 End Property
Public Property Set MarriedDaughter(C As Collection)
 Set pMarriedDaughter = C
 End Property

' Class MarriedMom:

Private pChildren As Collection   
Public Property Get Children() As Collection   ' ERROR HERE!
 Children = pChildren
End Property  
Public Property Set Children(C As Collection)
 Set pChildren = C
End Property

' Child Class:

Private pName As String    
Public Property Get Name() As String
 Name = pName
End Property    
Public Property Let Name(s As String)
 Let pName = s
End Property

And the Main Routine that tries to populate the classes:

Sub TestGrandMother()

' Create 3 Childs
Dim Child_1a As New Child: Child_1a.Name = "Bill"
Dim Child_1b As New Child: Child_1b.Name = "Sam"
Dim Child_2a As New Child: Child_2a.Name = "Sahar"

' Create 2 Married Daughters:
Dim Mamy1 As New MarriedMom
Dim Mamy2 As New MarriedMom

' Add the the children to the married daughters
Set Mamy1.Children = New Collection
Mamy1.Children.Add Child_1a
Mamy1.Children.Add Child_1b

Set Mamy2.Children = New Collection
Mamy2.Children.Add Child_2a

' Create Grandmother
Dim GrandMa As GrandMother: Set GrandMa = New GrandMother
Set GrandMa.MarriedDaughter = New Collection
GrandMa.MarriedDaughter.Add Mamy1
GrandMa.MarriedDaughter.Add Mamy2

' Now cycle childs Name and debug:
Dim aChild As New Child
For Each aChild In GrandMa.MarriedDaughter.Children
 Debug.Print GrandMa.MarriedDaughter.Children.Name
Next aChild

End Sub

and your question is??

cyboashu
– cyboashu

2018-09-09 08:14:56 +00:00
Commented Sep 9, 2018 at 8:14 — cyboashu
– cyboashu, Commented Sep 9, 2018 at 8:14

QHarr · Accepted Answer · 2018-09-09 09:08:40Z

1

In both cases where that error occurs you need to use the Set keyword as working with an object. That is just for the error type you comment on.

e.g.

Set Children = pChildren
Set MarriedDaughter = pMarriedDaughter

The following GrandMa.MarriedDaughter does not expose a .Children btw.

Perhaps

Dim aChild As MarriedMom, nextChild As Child   
For Each aChild In GrandMa.MarriedDaughter
    For Each nextChild In aChild.Children
        Debug.Print nextChild.Name
    Next
Next aChild

edited Sep 9, 2018 at 9:08

answered Sep 9, 2018 at 9:00

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Nested VBA collection of classes returning "Argument not optional" error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related