Class with property as collection of another class

**MCCCLXXXV** · 11-25-2008, 11:58 AM

I've just started to learn how to use classes, so before I explain my problem here, does anyone know of any online resources to learn more about classes in VBA? I read this already: http://www.cpearson.com/excel/Classes.aspx.

Anyway, here's my problem. I've created a class called CStock. It has 5 standard properties (with Get/Let methods) as explained in that article above. Nothing fancy here.

I also want to create a CPortfolio class that has 3 properties: pName, pNumberofPositions, and pHoldings. the first 2 are stardard, but the last one is different. I want it to somehow keep a collection of stocks. I tried putting:

Private pHoldings As Collection
Public Property Get Holdings() As Collection
    Holdings = pHoldings
End Property

Public Property Let Holdings(Value As Collection)
    pHoldings = Value
End Property

in the CPortfolio module. and then i tried calling:

port.Holdings.Add stk
port.Holdings.Add stk2

in a regular sub, but the sub goes into the Get instead of the Let and i get "Complie Error: Argument Not Optional"

Anyone know what I'm trying to do here? Cuz I'm not sure I even know. Also, after I get this working, I want to create a CBook class that holds a bunch of portfolios in the same manner. am i going about this the wrong way?

Thanks,
Joe

**shg** · 11-25-2008, 12:06 PM

A collection is an object variable; assignment requires a Set.

Would you please clean up the spelling of your thread title?

**MCCCLXXXV** · 11-25-2008, 12:31 PM

where would i put the set?

**Leith Ross** · 11-25-2008, 01:01 PM

Hello MCCCLXXXV,

Your definition is creating a private collection in this class. The user will not be able to assign or retrieve values from this collection. Is that what you want? It would be easier to answer your question if you provided an example of what you want to happen.

Sincerely,
Leith Ross

**romperstomper** · 11-25-2008, 01:06 PM

I would create methods to add and remove individual stocks/holdings, and change the Holdings property to:

Public Property Get Holdings() As Collection
    Set Holdings = pHoldings
End Property

if you need to return the entire set in one go.

**MCCCLXXXV** · 11-25-2008, 01:11 PM

I want to have a CPortfolio class that can hold stocks. So, for example, I have 5 portfolios of stocks to manage - each with a different name and a different number of holdings. I want to re-create these 5 portfolios in VBA such that each has its own name and each has its own stocks. In the end, I'd like to be able to access each stock within each portfolio separately, so i can reference stock #12 in portfolio B easily for example. Also, I'd like each stock to be an object of the CStock class, so that I can set multiple attributes for each stock.

Does that make sense? I'm going to be doing a lot of manipulations within each portfolio and across all portfolios and I just need a simple and clean way to access all the attributes of each portfolio. I tried doing this with multiple-dimension arrays, but it just got ugly and it was hard to remember which attribute was where.