Seeking VBA collection to store Build Of Materials (BOM) structure in

[mdlueck]

I am cleaning up some legacy VBA code (presently running in Office 2007) which calculates costs and weights for Build Of Materials (BOM) structures.

There is a hard coded max of 6 BOM levels.

The code which rolls up steps through BOM rows one at a time until it encounters an entry which is not a descendant of the parent the children records are being sought for.

I am just itching to replace the manual cursor / check code with OO Class based code. As the BOM hierarchy is researched make notes as to which row ID's are for which level, and then store that data in some sort of sorted collection class such that I can be certain stepping through the storage collection that once I find a non-descendant record, that there will not possibly be more records by virtue that the collection object maintains sort order... even if I append from either end of the collection.

I am thinking to just create six instances of what ever object type I end up selecting... one for each BOM level.

Then I would need to be able to retrieve records based on either their ID or value. Plus be able to cursor through the collection with Next / Prev operations.

So, search BOM level 3 for value X, retrieve that entry, and be able to then query what the Next / Prev BOM level 3 entry would be. Knowing that, I could better zero in on BOM level 4 records that would roll up to a particular level 3 entry. Ex: a given Level 3 entry is ID=14. The next Level 3 entry is ID 23. So I could then access the Level 4 collection, starting at 15 retrieve records completing with processing ID 22 as ID 23 in the BOM data is again at level 3.

That would save code time testing each entry to see if it is a direct descendant, over and over again...

So, suggestions of such a collection class? TIA!

 

[spikepl]

1. If something works and is operational, then "itch" alone is normally seldom a sufficient argument to touch it
2. If you insist on touching it, then adjacency list may not be the best for a BOM: http://dbaspot.com/sqlserver-programming/414583-bom-nested-set-model.html

 

[mdlueck]

If something works and is operational

Presently is is very ugly code I am working through auditing, optimizing, and commenting. Existing code had no comments, not all variables declared, did not use Option Explicit, and the algorithms were inefficient.

then adjacency list may not be the best for a BOM: http://dbaspot.com/sqlserver-programming/414583-bom-nested-set-model.html

That link looks SQL specific, not VBA variables. The database storage scheme is all worked out. I am seeking some type of VBA variable to organize the BOM data in so that it may be access directly bypassing seek/scan operations. With the BOM data having been enumerated fully once, it should not be necessary to perform additional seek/scan operations.