Understanding the generic VBA Collection object will make almost the entire object model more clear for both Autocad and Excel. Software like autocad and excel are pretty much a collection of collections of objects. The format is plural-singular, such that things-thing, layers-layer, worksheets-worksheet.
All these special collections have a close similarity to the generic VBA collection.
Collection.Add (object, strkey)
You can store (add to collection) elementary data types like integer and string, but objects are most useful. The objects do not have to be the same type, but in most custom extended versions, like worksheet and layer collections, the objects are all the same. The key must be unique. In VBA it cannot look like a number even if its a string. Its optional, but the point is storing and retrieving an object by name. There are two more arguments, before and after, which reference keys already in the collection, but they are not often used.
Collection.Item(indexno or strkey)
Item returns the object. If the index is an integer, it returns the object at that position in the list. If a string is passed it looks for the key. If it fails, it errors out. Item is the default method for Collection, so it can be omitted.
Collection(indexno or strkey)
This is why you usually type Worksheets(”Part”) instead of Worksheets.Item(”Part”). The name of the sheet is the unique key in the collection of Worksheets. Or you could type Worksheets(1) or Worksheets.Item(1)
In VBA an object, in this case a worksheet, is returned, requiring an assignment with the Set keyword,
Set ws = ThisWorkbook.Worksheets.Item(1)
You dont have to create or instantiate the Worksheets, but you do for your own Class objects, and Collections work just like a class.
Dim col As Collection
Set col = New Collection
col.Add ws
MsgBox col.Item(1).Name
Since I didnt use a key with Add, index by number is the only option to retrieve.
Collection.Count returns the total number of items
Collection.Remove(indexno or strkey) removes that member.
Thats it, thats the entire oeuvre of Collection.
Indexing is automatic by the Collection. If an item is removed, the trailing indexes move up. You can specify where in the collection to Add, with Before and After, but once it’s there it’s there.
There is a special Loop feature, custom collections can loop with
For Each object in objects
. . .
Next Object
The other technique is
For inc = 1 to col.count
. . .
Next inc
Collections are like arrays, only one dimensional, and they have a name key for retrieval in addition to index location. How then would we use a Collection to save a table of data? By making a class object for each row.
Lets look at how autocad implements Layers. The Layer Properties Manager is a table. Properties are column headings across the top, layer names are rows in column 1.
To create a new layer,
Dim layer As AcadLayer
Set layer = acaddoc.Layers.Add(“string”)
(acaddoc is my connector from excel which contains the ThisDrawing object)
The Collection class Layers is already created. The collection is available just like Worksheets is available. We are creating a new instance of the Layer object, adding a new row. We use the Add method of collection Layers. What we are passing is the string key, not a created object (like we do in the VBA Collection). The Layers Class is creating the Layer object using the key and adding it to the collection. Its passing back the new Layer object to our variable. It created the layer with all default attributes, so now can set the rest of the attributes.
layer.LineType = “Center”
layer.Color = acRed
This is how a lot of objects are made, from a method in the collection. The bare minimum of attributes are required to create. The object is passed back to set the remaining properties.
In the autocad object model, (hold down CTRL and it will open in a new window)
https://help.autodesk.com/view/OARX/2019/ENU/?guid=GUID-A809CD71-4655-44E2-B674-1FE200B9FE30
As you go down the hierarchy, objects and collections alternate. Rectangles are collections and ovals are objects (see key at bottom). Top level is the application object which contains the Documents collection which contains a Document object. The Document has a pointer to Layers collection. It also has a pointer to another collection ModelSpace which has all the visible objects. Modelspace collection has not one Add method to create objects but about 50 different ones, such as AddLine, AddText, etc. Click on ModelSpace and you will see them. All of them pass back their newly created object for further possible modification of attributes.
Since ModelSpace is a collection, you can iterate through it with the collection style loop.
Dim acad_ent As AcadEntity
For Each acad_ent In acaddoc.ModelSpace
Debug.Print acad_ent.ObjectName
Next acad_ent
Or retrieve with Item
Dim inc As Integer
For inc = 0 To acaddoc.ModelSpace.count – 1
Debug.Print acaddoc.ModelSpace.Item(inc).ObjectName
Next inc
I am not going to try to explain when counts start with 1 and when they start with 0. Its about 50-50. Some programming pioneer must have insisted on 0. Imagine buying a bag of 6 apples from 0 to 5.
Back to our story,
Our BOM goal is to create a BOM sheet, looping through our parts table join. We will have the raw data in an array, and we want one collection object to populate TreeView, ListView and Worksheet. Our parts have potentially both parents and children. I want 3 levels of parts on the DWG BOM indented, from a practical POV, and i want nearly unlimited levels from a programmers POV. Parts have quantities needed for one assembly, and higher total quantities based on assembly quantities. We will make an object class called Bom_Record and a collection class called Bom_Records. If we can duplicate the behavior of the Layers example, we will know our classes are set up correctly.
Set layer = acaddoc.Layers.Add(“string”)
Set Bom_Record = Bom_records.Add(strpart_ID)
A unary table relationship, where the relationship is between instances of a single entity type, parts in our case, is called a recursive relationship. A part cannot be composed of its own self, but it is composed of other parts. To traverse a deeply nested part a recursive program is required. One way to start writing this is to expect only 3 levels, write the code, and watch for where the code starts repeating itself. Write a boolean function called Hasparts(part_id), if it returns true, then pass the current part_id to a sub that makes a list of the sub-parts. The treeview control and the listview control both have a display entry and a key. Assemble this key as a complete path to the part. To populate the treeview the parent always has to be known. I am going to post the code in raw but working form so the project can be taken to a finish. There are still some other features, such as linking the treeview to the listview, writing the listview to a sheet. I am going to finish the project in visual studio, at a later date. First i need to do some smaller projects in vb.net.
'entry top level uses global var Records
'g_assy_ID = assy_id
Sub make_col(assy_id As String, assy_qty As Integer)
're-sets the collection for input
Set Records = New Bom_Records
Call make_BOM_Records(assy_id, assy_qty)
End Sub
Sub make_BOM_Records(assy_id As String, assy_qty As Integer)
'the global var Records set previous
'so it can either be new or a continuation
Dim strkey As String
Dim ar As Variant
Dim record As bom_record
ar = return_row("PART", assy_id)
strkey = assy_id & "\"
Set record = Records.Add(strkey)
record.assy_id = assy_id
record.part_id = ""
record.subpart_id = ""
record.qty = assy_qty
'record.ex_qty =
record.UM = ar(1, 3)
record.desc = ar(1, 2)
'no parent - root record
record.parent_key = ""
record.mlevel = 1
Debug.Print " "
Debug.Print "new run"
Debug.Print strkey
'begin subparts
If has_parts(assy_id) Then
Call collect_parts(assy_id, assy_qty, 2, strkey)
End If
End Sub
'populates global var Records
'recursive, assy_id is not necessarily top level
Sub collect_parts(assy_id As String, assy_qty As Integer, int_level As Integer, parent_key As String)
Dim ar As Variant
Dim part_id As String, strkey As String
Dim part_qty As Integer, ex_qty As Integer
Dim part_um As String, part_desc As String
Dim rows As Integer, r As Integer
Dim record As bom_record
If int_level > g_max_level Then 'normal max is 3 or 9
'MsgBox "bom level too high"
Debug.Print "exit collect parts sub at max_level = " & int_level
Exit Sub
End If
'returns 5 col joined table for assy_id\parts
ar = get_parts(assy_id)
rows = UBound(ar, 1)
For r = 1 To rows
'assy_id = ar(r, 1) true for all rows
part_id = ar(r, 2)
part_qty = ar(r, 3)
part_desc = ar(r, 4)
part_um = ar(r, 5)
ex_qty = assy_qty * part_qty 'passed in assy_qty
'key is simplicity
strkey = parent_key & part_id & "\"
Set record = Records.Add(strkey)
'the dwg designer wants an indented excel bom with just 3 levels
'the programmer wants a 2nd option for nearly unlimited levels
'there is no subpart id coming in from the array, only parent and child
'i want bom record to be a finished product, so we need logic here
'to explicitly save 3 levels as assy, part and subpart
'when level# is > 3 still use subpart for id ; key is accurate complete path\
record.assy_id = ""
If int_level = 2 Then
record.part_id = part_id
record.subpart_id = ""
Else '3 or more
record.part_id = ""
record.subpart_id = part_id
End If
record.qty = part_qty
record.ex_qty = ex_qty
record.UM = part_um
record.desc = part_desc
record.parent_key = parent_key
record.mlevel = int_level
Debug.Print strkey
'recursive call, increment level, pass the parent key
If has_parts(part_id) Then
Call collect_parts(part_id, ex_qty, int_level + 1, strkey)
End If
Next r
End Sub
At this time (this is working but not polished code) I have nothing in the bom_record class except public variables. It could be a structure instead.
in class module Bom_Record
Public assy_id As String Public part_id As String Public subpart_id As String Public qty As Integer Public ex_qty As Integer Public UM As String Public desc As String Public mlevel As Integer Public mkey As String Public parent_key As String
Class Bom_Records though has methods that mimic Add and Item. It has only one private variable, the collection object. The Add method creates a New Bom_Record, just like Layers.Add creates a new layer object. Remove always has issues so it is left out, just like autocad VBA cannot allow Remove without checking every single entity in drawing to see if the object is being referenced.
in Class module Bom_Records
Option Explicit
Private col_records As Collection
Private Sub Class_Initialize()
Set col_records = New Collection
End Sub
Public Function Add(ByVal strkey As String) As bom_record
Dim objrecord As New bom_record
objrecord.mkey = strkey
col_records.Add objrecord, strkey
Set Add = objrecord
End Function
Public Function Item(ByVal varID As Variant) As bom_record
Set Item = col_records.Item(varID)
End Function
Property Get count() As Long
count = col_records.count
End Property
Property Get col() As Collection
Set col = col_records
End Property
