CODEWITHSUNDEEP
vbaexcel
user3486991
user3486991

Reputation: 463

Using collections as properties of an object in VBA

With class module "Class1Test" as 

Private pGreetings As Collection

Public Property Get Greetings() As Collection
Greetings = pGreetings
End Property
Public Property Let Greetings(Value As Collection)
pGreetings = Value
End Property

If I run the sub

Dim MyPhrases As Class1Test
Public Sub Test()
Set MyPhrases = New Class1Test
MyPhrases.Greetings.Add "Have a nice day"
End Sub

I get the a compile error "Argument not optional"

Why can't I add the string to the the collection myphrases.greetings ? Please forgive the newbie question. Just learning VBA.

Upvotes: 0

Views: 1445

Answers (1)

David Zemens
David Zemens

Reputation: 53663

A few things wrong.

Collection is an object, so you must use the Set keyword when assigning. Also in the Let procedure for consistency in naming conventions, I would use lGreetings instead of Value although that should not really matter.

Private pGreetings As Collection

Public Property Get Greetings() As Collection
    Set Greetings = pGreetings
End Property
Public Property Let Greetings(lGreetings As Collection)
    Set pGreetings = lGreetings
End Property

This will still raise an 91 error (Object variable or with block not set) because you have not instantiated the collection object. Probably the way you should do this is in the class module's Initialize routine.

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

Upvotes: 3

Related Questions