Using collections as properties of an object in VBA

Question

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.

David Zemens · Accepted Answer · 2014-04-10 00:07:42Z

3

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

edited Apr 10, 2014 at 0:07

answered Apr 10, 2014 at 0:01

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

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

Collectives™ on Stack Overflow

Using collections as properties of an object in VBA

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