How do I reference classes that have a relationship with each other

Hi all, been a wee while.

I'm trying to figure out how to reference one class within another that has a reference back to the first class again. For example, ActiveSheet has a Parent property, which has an ActiveSheet property, which has a Parent property, and so on. I want to do something similar.

I want to create two classes, one that stores details of an Organisation and one that stores details of a Department. One of the properties of an Org should return the Dept class that the Org belongs to (one Org has one Dept) and one of the properties of a Dept should be an array (or collection?) of Org classes that belong to it (one Dept has many Orgs). But I can't figure out the logic without causing a stack error.

Anybody any experience they can lend me here?

Thanks all.

If you're happy taking me through it Greg, I can give you whatever info you need. Also, if you realise I'm going about this completely the wrong way let me know.

I use my PERSONAL.XLSB extensively, the procedures within often repeating each other in parts. I'm trying to overhaul all this, using objects effectively. Most of the procedures need to be able to determine details about the organisations we have on the books so I'm starting there. The classes I want to create are effectively going to be used to store constants, in that the data within them won't change.

So, the way I was thinking was to have classes to store individual orgs and a further class that acts as a collection of the orgs. It will be this collection class that I then reference in my code using something like

Please Login or Register  to view this content.

To build the collection, the Class_Initialize for clsOrgs calls an Add procedure for each org that we have on the books, with the data for each org as the arguments. It was when I was repeating the dept info for several orgs that I realised that the dept should probably be an object in it's own right. And since there are several departments, they should also have a collection class. That was my thinking in terms of the concept anyway, and attached is where I'm up to in terms of the coding for it. I've sanitised the data in the Class_Initialize, but what is there is representative of how the orgs and depts relate to each other.

Here's what I tried. It might get you started. Code for clsOrg:

Please Login or Register  to view this content.

Code for clsDept:

Please Login or Register  to view this content.

Then a quick test macro:

Please Login or Register  to view this content.

WBD

Dix, could you take a look at the attachment. I think I was editing that post to include the attcahment when you posted yours. I'd very much appreciate your guidance too.

Hmmm. You're capturing the dept/org relationship twice which is confusing. Let me think about this.

WBD

OK. I think you had too many objects kicking around. I've tried to simplify this a bit. See attached which is based on my previous suggestion but has kept most of your code. Look in the modTest module.

WBD

Dix, thanks so much for spending some time on this. I just realised you'd replied. I'm off work atm so I'll digest it properly when I get back.

Initially though I can see that the clsOrgs data is loaded into the class from outside the class module. Is there no way to do that from within the class? I'd like to make these class modules easy to supplant into other projects without having to dig out where the data is loaded from too. In a perfect world, I'd just like to use the New keyword to create the object, and all the data would be there.

Thanks again man.

I think your main problem was that you were trying to create the relationships entirely in the class constructors but that didn't then correctly encapsulate the relationship between the classes.

WBD