Best Data Structure for list of parts with assemblies, sub-assemblies, and such in VBA?

Question

I'm working on a project that is basically a hack on top of a hack on top of an excel sheet and unfortunately we don't have time to re-factor but the core code simply isn't fast enough.

We have assemblies which are made out of sub-assemblies and parts. So an assembly is the super structure and a part is the smallest structure.

We have a BoM, bill of materials, that we put into Excel through various methods (manual, import, parse... etc) and this is put into a tree structure, not a heap, a tree.

My problem with this is, the parts don't need to be in any particular order, they do need to be searched but I don't see any advantage to having this as a tree.

I thought a better structure would be a Linked List within a Linked List within a Lin... so on and so forth.

Can anyone suggest a better approach?

EDIT FOR MORE DETAIL: Assemblies have several properties such as mass, cg, inertia, so on and so forth, they are objects that should point to several other objects parts, which have the same properties as assemblies and more. Assemblies can also contain other assemblies.

Answer 1

I personally would try hash tables. I'm not a VBA programmer, but judging from some Google searches there are ways to use Collection or Dictionary to hack something together. For what its worth, I got my information from here.

-Brett

Answer 2

It's possible to set up a series of dependant Lists, eg select item "widget" in List1 and List2 is populated with all the items that pertain to item "widget", and so on. Search "Conditional Lists" and possibly "dynamic names" in one of the main Excel groups for examples. Typically these are used with a series of DropDowns. Problem is, depending on respective list sizes and numbers of branches, you can quickly end up with something very difficult to manage. An alternative approach is a relatively simple database. A single list of parts, with description and number, each having one or more categories, a category could point to a "parent" part. You can then filter on the categories. Not sure what the overall objective is apart from merely "search". Excel does that pretty fast itself, even with large non-sorted lists.

Answer 3

It is possible to use ADO with Excel. A table can be a whole sheet, a range or a named range. You have the power of the Jet engine, so you can run any query that the engine supports, including the use of JOINS and sub queries.