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)
