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:

Please Login or Register  to view this content.

in the CPortfolio module. and then i tried calling:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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.

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

Yes you can have methods in the CPortfolio class to add, remove or retrieve individual CStock items to/from the private collection.

**MCCCLXXXV** · 11-25-2008, 02:33 PM

ok that sounds good. I'm not exactly sure how to create/use any other method besides let and get. I tried adding this to the CPortfolio class module:

Please Login or Register  to view this content.

and in my regular module i have this:

Please Login or Register  to view this content.

When I step through, It goes into the AddStock sub, but when it gets to pHoldings.Add Value, it give me the following error:

"Runtime error 91: Object variable or With block variable not set"

I have

Please Login or Register  to view this content.

in the CPortfolio class header. Is there something i'm missing?

**MCCCLXXXV** · 11-25-2008, 02:40 PM

ok i added:

Please Login or Register  to view this content.

to CPortfolio and called port.Initialize and the addstock seems to work now. Is this a proper solution?

Also, now that this is working, how do I access elements of this collection in my regular module? for example, how can I get the price of the stock with ticker "GOOG" that resides in the portfolio with the name "B"?

Thanks for your help.