VBA custom data model problem: define array of one class as public member of another class

[VBAchallenge]

Hello,

I am trying to setup a data architecture for VBA based business simulator. There will be more devs in few weeks but for now I am the lead, tasked with initial project setup.

General question is: what would you advice for a definition of custom class, holding a public array of variable number of other class objects? How to properly define such data model? Is there any good book on advanced data structures in Excel VBA that you would recommend (I have no problem with reading and learning)?

Context: the problem I am stuck with is related to custom class design in VBA. I am looking for an elegant way to design objects (classes) and methods. The business simulation environment is defined as: WORLD, MARKETS, SEGMENTS (of customers), COMPANIES, PRODUCTS (of companies). World holds markets, every market holds some segments and some companies, every company holds its products:

WORLD
-MARKET1
--SEGMENT1
--SEGMENT2
--COMPANY1
---PRODUCT1
---PRODUCT2
--COMPANY2
---PRODUCT3
---PRODUCT4
-MARKET2
...

It would be natural to include all markets in world, segments and companies in market and products in company. Therefore I have planned five respective classes and hit the wall while trying to define table (array) of markets as public member of the world class. Public is important here because there will be lots of interactions with markets and individual members of the market class so building all possible market methods to access all this data would not serve anybody.

[in worldClass module]

Public name as String
Public ID as Integer
Public number_of_markets as Integer
Public markets() as marketClass             ' <-- error here

[in marketClass module]
Public name as String
Public ID as Integer
... other market-related data

This obviously got rejected by VB ("constants, fixed-length strings, arrays, user-defined types, and declare statements not allowed as public members of an object module"). I've already searched all around building custom VBA classes, exposing private arrays via Set/Get property, storing reference in (public) Object member of my class. I couldn't find any working solution for my environment. And I also don't understand the purpose of such limitation in VB. I found are nice solutions for array of base types objects in class, like array of strings, but no solutions for array of class2 as member of class1.

However other Excel objects seam to have this structure: Workbook.Worksheets() is a public array of variable number of one class objects (worksheets) included in another class (workbook). And worksheet's members are public: Workbook.Worksheets(1).Cells(1,2).Value...

In C/C++ the solution would be to define in world class a pointer to markets table and manage memory allocation for markets. But VBA does not allow to use pointers and does all memory management automagically in the background. So how do you design more complex data structures in VBA?

I don't think it matters here, but for the record and to satisfy forum rules: I am using Win7.SP1.Pro/32b and Excel 2013.

I'm seasoned IT engineer with solid experience in C and some in C++ but did not do serious coding for almost 20 years...


Best regards,
Piotr

 

[RoryA]

Why can't you use properties/methods as accessors of private array variables?

BTW workbook.Worksheets is not a public array. It's a collection.

 

[VBAchallenge]

Rory, first: thanks for looking at my problem.

Good question! Now, when I think about it, I guess I was expecting too many methods, all doing simple data access or data entry. No extra logic, so why not expose and access members directly from wherever needed. And I was thinking of using the top class (world) as sort of data holder, enabling easy, self-explanatory and self-documenting access to my data model. Like: world.market(1).segment(1).population_growth_trend=1.03 or world.market(2).company(1).product(2).get_margin. Plus I guess I just wanted to break that wall, head-first

Now, this collection hint -- it IS an inspiration. I'll sure look down this path.

How about some literature on creating collections of custom classes? Or any other recommendations for guides on custom classes?


Best regards,
Piotr