Transformation Matrices – 2D Demo

The theory of matrices was first developed (or so I read) in relation to geometric transformations.

The best single reference that explained Transformation Matrices to me esp with regards to graphics programming was Rod Stephens book Visual Basic Graphics Programming, which is 20+ years old. It’s a comprehensive but clear introduction. There are also many linear algebra math videos on Khan, Youtube etc that i am just starting to explore. Stephens multiplies his vector from the left of the matrix. Everyone else does it from the right, so i cannot use his examples as-is, but looking at his methods has been very helpful.

A vector is also a matrix, and its a point. Multiplying a square matrix by a column vector yields another column vector. Thats how the point gets transformed. The transformation matrix can contain numbers for scaling, translating (moving) and rotation.

The basic idea can be shown with a 2D autocad implementation and then extend to full 3D. Matrix math, and multiplication of matrices is somewhat hard to grasp, but programming it from scratch is good reinforcement. The basic 3D matrix is a 4×4 grid of doubles. The 2D matrix is 3×3.

The first sub initializes an identity matrix, which is the base. The subs that create Translation, Scaling and Rotation numbers will modify the identity matrix. To combine these operations into one, multiplication of two 3×3 matrices is done. Finally to apply the matrix to a point, the matrix is multiplied by the 1×3 point vector.

In reality, we are going to use arrays as matrices, and the array of a 1×3 is actually just a 3, but we are going to treat it as a column. arrays start with index zero. Most matrix references or maybe all use 1 as the base number.

Here is the 3×3 initial matrix with value 1 on the main diagonal and zero elsewhere. Its a function that returns the result and does not take any arguments.

show_mat(M) is a sub to show the matrix in the immediate window screen for checking.

    Public Function m3x3_ID() As Double(,)
        Dim M(0 To 2, 0 To 2) As Double
        Dim i, j As Integer

        For i = 0 To 2
            For j = 0 To 2
                If i = j Then
                    M(i, j) = 1
                    M(i, j) = 0
                End If

        Return M
    End Function

    Public Sub show_mat(M(,) As Double)
        Dim i, j As Integer
        Dim str As String = ""
        For i = 0 To UBound(M, 1)
            For j = 0 To UBound(M, 2)
                If j = 0 Then
                    str = str & vbCrLf
                End If
                str = str & M(i, j) & vbTab

    End Sub

We need a sub to multiply a 3×3 matrix by a 1×3 vector. MxV. This is a simple program that takes the Matrix and Vector as arguments and returns a Vector.
For 2D the Z value is not required but is required by autocad, leading to a bit of complication we wont have with 3D.

    Function MxV(M(,) As Double, V() As Double) As Double()
        Dim V2(2) As Double

        V2(0) = M(0, 0) * V(0) + M(0, 1) * V(1) + M(0, 2) * 1
        V2(1) = M(1, 0) * V(0) + M(1, 1) * V(1) + M(1, 2) * 1
        V2(2) = M(2, 0) * V(0) + M(2, 1) * V(1) + M(2, 2) * 1

        'need to zero this out for 2D demo purpose
        V2(2) = 0

        Return V2

    End Function

Matrices are combined by multiplication. I still have to show myself why that works, when rotation and scaling use the same cells. The multiplication of two 3×3 matrices is row by column. You could do it with brute force, but studying the concise math definition –

c(ij) = a(i1)b(1j) + a(i2)b(2j) + a(i3)b(3j)

– shows the basic i and j loop variables and the third variable just goes from 1 to 3. Modifying slightly for our 0 to 2 array gives a 3 variable loop. Matrices A and B are 3×3. The order matters. Multiplication is not commutative. A movement then a rotation is different than a rotation then a movement. The function returns the new matrix.

    Function MxM(A(,) As Double, B(,) As Double) As Double(,)
        Dim C(2, 2) As Double
        Dim i, j, n As Integer
        Dim value As Double

        For i = 0 To 2
            For j = 0 To 2
                value = 0
                For n = 0 To 2
                    value = value + A(i, n) * B(n, j)
                C(i, j) = value

        Return C
    End Function

Converting Autocad VBA ActiveX to Visual Studio

The Autocad ActiveX and VBA Developer’s Guide, Work in Three Dimensional Space (chap 8 in the old print version), is available with 10 sample code programs here

also in your autocad installation as a CHM file, (very easy to work with)
ProgramFiles\Common Files\Autodesk Shared\acadauto.chm

these sample programs paste in to Visual Studio with a few but not too many problems. Below is a screenshot of the first one. On the left is the virgin code. On the right is the corrected version which runs as intended.

Visual Studio throws up 8 errors on paste. (see ThisDrawing discussion below)
Let and Set assignment is no longer supported. You simply delete them.
acRed and acBlue is not declared. Hovering over the word acRed, Visual Studio suggests the fix, add the namespace ACAD_COLOR
Variant is no longer a supported type: Use the Object type instead.

Changing Variant to Object does eliminate the error on that line, but creates errors where it is accessed. Arrays are an area where has fixed logical inconsistencies in VBA. Autocad declared a return value of variant because static arrays could not accept an assigment. Dynamic arrays could accept assignment, but that was not in the very first version of VBA, and apparently never made it into the Autocad ActiveX implementation. There is no longer a distinction in between dynamic and static arrays.

We know the point data for polylines is a Double, and we know the autocad Coordinates method will return a variable number of points, so lets declare the variable as we would a double type dynamic array.

Dim get2Dpts() as Double

now we get an error on assigment –
get2Dpts = pline2DObj.Coordinates
Option Strict On disallows implicit conversions from ‘Object’ to ‘Double’.

anytime you get that and you know your variables are convertible the solution is Ctype(var, type)
get2Dpts = CType(pline2DObj.Coordinates, Double())

Autocad VBA sample code references a variable called ThisDrawing which is always available in the VBA environment. From Visual Studio, I run a sub called Connect_Acad that makes the connection and uses a global AcadDocument variable which can be named ThisDrawing and that is what i have done here. It is added to the top of the sample code.

Make those changes and it runs as is.

There is a simplification on the array assignments available in Visual Studio. In VBA I made subroutines to try to make the awkward point data assigments flow easier. VB.Net has a good new array feature called an initialization list.

you can change all this –

Dim points2D(0 To 5) As Double
Dim points3D(0 To 8) As Double

‘ Define three 2D polyline points
points2D(0) = 1 : points2D(1) = 1
points2D(2) = 1 : points2D(3) = 2
points2D(4) = 2 : points2D(5) = 2

‘ Define three 3D polyline points
points3D(0) = 1 : points3D(1) = 1 : points3D(2) = 0
points3D(3) = 2 : points3D(4) = 1 : points3D(5) = 0
points3D(6) = 2 : points3D(7) = 2 : points3D(8) = 0

to this –

‘ Define three 2D polyline points
Dim points2D() As Double = {1, 1, 1, 2, 2, 2}

‘ Define three 3D polyline points
Dim points3D() As Double = {1, 1, 0, 2, 1, 0, 2, 2, 0}

side by side screenshots –

9 errors on paste
Method arguments must be enclosed in parentheses.
acWorld and acUCS is not declared.

fix those and down to 4 all due to Variant.

Dim WCSPnt As Variant
Dim UCSPnt As Variant

Lets try that again where we change the variant array to its target double array.
Dim WCSPnt() As Double
Dim UCSPnt() As Double

Again we get an error where Autocad Utility Getpoint is returning an Object, originally it was a Variant. We know its a double array so we can use the Ctype function.

The other and last error though is on the boolean argument in Utility.TranslateCoordinates.
UCSPnt = ThisDrawing.Utility.TranslateCoordinates(WCSPnt, AcCoordinateSystem.acWorld, AcCoordinateSystem.acUCS, False)
Strict On disallows implicit conversions from ‘Boolean’ to ‘Integer’.

That makes me want to look at the ActiveX Reference Guide under Objects Utility TranslateCoordinates. Its a flag possible values are True or False but its a Long type not Boolean. it affects whether the first argument is treated as an absolute point value or a vector. i dont quite know why its implemented like that but i can go with the value zero to get the intent.

That allows another error which has been in the queue on the same line. which is fixed by the familiar Ctype function.
And now it runs.

Arrays in VB.Net are all normally zero-based (with some exceptions). So VBA declarations such as Dim pt (0 to 2) as double can be changed to Dim pt(2) as Double. But we can also use the initialization list to clean up.

change this –
Dim origin(0 To 2) As Double
Dim xAxisPnt(0 To 2) As Double
Dim yAxisPnt(0 To 2) As Double

origin(0) = 4 : origin(1) = 5 : origin(2) = 3
xAxisPnt(0) = 5 : xAxisPnt(1) = 5 : xAxisPnt(2) = 3
yAxisPnt(0) = 4 : yAxisPnt(1) = 6 : yAxisPnt(2) = 3

to this –
Dim origin() As Double = {4, 5, 3}
Dim xAxisPnt() As Double = {5, 5, 3}
Dim yAxisPnt() As Double = {4, 6, 3}

    Sub Ch8_NewUCS()
        Call Connect_acad()
        Dim ucsObj As AcadUCS

        Dim origin() As Double = {4, 5, 3}
        Dim xAxisPnt() As Double = {5, 5, 3}
        Dim yAxisPnt() As Double = {4, 6, 3}

        ' Add the UCS to the UserCoordinatesSystems collection
        ucsObj = ThisDrawing.UserCoordinateSystems.Add(origin, xAxisPnt, yAxisPnt, "New_UCS")
        ' Display the UCS icon
        ThisDrawing.ActiveViewport.UCSIconAtOrigin = True
        ThisDrawing.ActiveViewport.UCSIconOn = True

        ' Make the new UCS the active UCS
        ThisDrawing.ActiveUCS = ucsObj
        MsgBox("The current UCS is : " & ThisDrawing.ActiveUCS.Name & vbCrLf & " Pick a point in the drawing.")

        ' Find the WCS and UCS coordinate of a point
        Dim WCSPnt() As Double
        Dim UCSPnt() As Double

        WCSPnt = CType(ThisDrawing.Utility.GetPoint(, "Enter a point: "), Double())
        UCSPnt = CType(ThisDrawing.Utility.TranslateCoordinates(WCSPnt, AcCoordinateSystem.acWorld, AcCoordinateSystem.acUCS, 0), Double())

        MsgBox("The WCS coordinates are: " & WCSPnt(0) & ", " & WCSPnt(1) & ", " & WCSPnt(2) & vbCrLf &
     "The UCS coordinates are: " & UCSPnt(0) & ", " & UCSPnt(1) & ", " & UCSPnt(2))
    End Sub

8 errors on paste
Set, Arguments not enclosed in parentheses, unfound enumerations that need namespaces, and use of Variant arrays.

Dim firstVertex As Variant
firstVertex = plineObj.Coordinate(0)

looking up Polyline Object in the ActiveX Rerference Guide then clicking on Coordinate says the property value is a two or three element array of doubles.

change to –
Dim firstVertex() As Double
firstVertex = CType(plineObj.Coordinate(0), Double())

Dim coordinateWCS As Variant
coordinateWCS = ThisDrawing.Utility.TranslateCoordinates(firstVertex, AcCoordinateSystem.acOCS, AcCoordinateSystem.acWorld, False, plineNormal)

change to –
Dim coordinateWCS() As Double
coordinateWCS = CType(ThisDrawing.Utility.TranslateCoordinates(firstVertex, AcCoordinateSystem.acOCS, AcCoordinateSystem.acWorld, 0, plineNormal), Double())

    Sub Ch8_TranslateCoordinates()
        Call Connect_acad()
        Dim plineObj As AcadPolyline
        Dim points() As Double = {1, 1, 0, 1, 2, 0, 2, 2, 0, 3, 2, 0, 4, 4, 0}

        ' Create a light weight Polyline object in model space
        plineObj = ThisDrawing.ModelSpace.AddPolyline(points)

        ' Find the X and Y coordinates of the first vertex of the polyline
        Dim firstVertex() As Double
        firstVertex = CType(plineObj.Coordinate(0), Double())

        ' Find the Z coordinate for the polyline using the elevation property
        firstVertex(2) = plineObj.Elevation

        ' Change the normal for the pline so that the difference between the coordinate systems is obvious.
        Dim plineNormal() As Double = {0#, 1.0#, 2.0#}
        plineObj.Normal = plineNormal

        Dim coordinateWCS() As Double
        coordinateWCS = CType(ThisDrawing.Utility.TranslateCoordinates(firstVertex, AcCoordinateSystem.acOCS, AcCoordinateSystem.acWorld, 0, plineNormal), Double())

        ' Display the coordinates of the point
        MsgBox("The first vertex has the following coordinates:" & vbCrLf & "OCS: " & firstVertex(0) & ", " &
     firstVertex(1) & ", " & firstVertex(2) & vbCrLf & "WCS: " & coordinateWCS(0) & ", " & coordinateWCS(1) & ", " & coordinateWCS(2))
    End Sub

here is one we can clean up quite a bit. it pastes in with only two errors, both trivial. It runs just by deleting the word Set, adding AcadApp. in front of ZoomAll and adding Connect_acad as the first line .
Here is the original VBA from Autocad.

Sub Ch8_Create3DMesh()
    Dim meshObj As AcadPolygonMesh
    Dim mSize, nSize, Count As Integer
    Dim points(0 To 47) As Double

    ' create the matrix of points
    points(0) = 0: points(1) = 0: points(2) = 0
    points(3) = 2: points(4) = 0: points(5) = 1
    points(6) = 4: points(7) = 0: points(8) = 0
    points(9) = 6: points(10) = 0: points(11) = 1
    points(12) = 0: points(13) = 2: points(14) = 0
    points(15) = 2: points(16) = 2: points(17) = 1
    points(18) = 4: points(19) = 2: points(20) = 0
    points(21) = 6: points(22) = 2: points(23) = 1
    points(24) = 0: points(25) = 4: points(26) = 0
    points(27) = 2: points(28) = 4: points(29) = 1
    points(30) = 4: points(31) = 4: points(32) = 0
    points(33) = 6: points(34) = 4: points(35) = 0
    points(36) = 0: points(37) = 6: points(38) = 0
    points(39) = 2: points(40) = 6: points(41) = 1
    points(42) = 4: points(43) = 6: points(44) = 0
    points(45) = 6: points(46) = 6: points(47) = 0

    mSize = 4: nSize = 4

    ' creates a 3Dmesh in model space
    Set meshObj = ThisDrawing.ModelSpace. _
 Add3DMesh(mSize, nSize, points)

    ' Change the viewing direction of the viewport
    ' to better see the cylinder
    Dim NewDirection(0 To 2) As Double
    NewDirection(0) = -1
    NewDirection(1) = -1
    NewDirection(2) = 1
    ThisDrawing.ActiveViewport.Direction = NewDirection
    ThisDrawing.ActiveViewport = ThisDrawing.ActiveViewport
End Sub

code that runs in visual studio

   Sub Ch8_Create3DMesh()
        Call Connect_acad()
        Dim meshObj As AcadPolygonMesh
        Dim mSize, nSize, Count As Integer

        Dim points() As Double =
            {0, 0, 0,
             2, 0, 1,
             4, 0, 0,
             6, 0, 1,
             0, 2, 0,
             2, 2, 1,
             4, 2, 0,
             6, 2, 1,
             0, 4, 0,
             2, 4, 1,
             4, 4, 0,
             6, 4, 0,
             0, 6, 0,
             2, 6, 1,
             4, 6, 0,
             6, 6, 0}

        mSize = 4 : nSize = 4
        ' creates a 3Dmesh in model space
        meshObj = ThisDrawing.ModelSpace.Add3DMesh(mSize, nSize, points)

        ' Change the viewing direction of the viewport 
        Dim NewDirection() As Double = {-1, -1, 1}
        ThisDrawing.ActiveViewport.Direction = NewDirection
        ThisDrawing.ActiveViewport = ThisDrawing.ActiveViewport
    End Sub

sample code from object UCS method GetUCSMatrix in ActiveX Reference Guide

Here is the corrected code. I have added a loop to read and print the matrix values.
The orig program creates a new UCS, makes it active, then draws a circle. That illustrates that coordinate input from ActiveX is always interpreted as World coordinates (VBA ignores the current UCS). The program then runs

newMatrix = UCSobj.GetUCSMatrix

which moves the circle from its world coordinates to the equivalent UCS coordinates.

We never have to examine the Matrix or know what its dimensions are. I added a loop to read it. The matrix is a square 4×4. It contains point values as columns. The bottom row seems to be unused.

col1 is the same data as the variable UCSXDIR, its the X vector from the UCS origin.
col2 is the same data as the variable UCSYDIR, its the Y vector from the UCS origin
col3 is the Z vector, which is obtainable from the XY vectors by the right hand rule.
col4 is the origin.

if we were writing our own matrix we would do

dim Matrix(3 , 3) as Double


dim Matrix( , ) as Double =
{ {1,0,0,2},
{ 0,1,0,2},
{ 0,0,1,0},
{ 0,0,0,1} }

these are direction vectors.
UCSXDIR = 1,0,0
UCSYDIR = 0,1,0
Z DIR = 0,0,1

absolute point
UCSORG = 2,2,0

this is not the whole story, Transformation Matrices are a fascinating topic in linear algebra. TransformBy has some useful examples, including scaling, which occurs on the main diagonal. ARX Developers Guide (search on transformation matrix) has better help page than ActiveX. First 3 columns are both rotation and scaling. last column is translation. Transformation matrixes can be combined so translation, scaling and rotation are all accomplished in a single operation.

TransformBy is a method of the elementary AcadEntity, meaning it is enabled for every object in the drawing.

Sub Example_GetUCSMatrix()
        ' This example creates a new UCS and finds the UCS matrix for it.
        ' It then creates a circle using WCS coordinates and
        ' transforms the circle for the UCS.
        Call Connect_acad()

        ' Define a new UCS and turn on the UCS icon at the origin.
        Dim ucsObj As AcadUCS
        Dim origin() As Double = {2, 2, 0}
        Dim xAxisPoint() As Double = {3, 2, 0}
        Dim yAxisPoint() As Double = {2, 3, 0}

        'Dim xAxisPoint() As Double = {4, 4, 0}
        'Dim yAxisPoint() As Double = {0, 4, 0}

        ucsObj = ThisDrawing.UserCoordinateSystems.Add(origin, xAxisPoint, yAxisPoint, "UCS1")
        ThisDrawing.ActiveUCS = ucsObj

        Dim vportObj As AcadViewport
        vportObj = ThisDrawing.ActiveViewport
        vportObj.UCSIconOn = True
        vportObj.UCSIconAtOrigin = True
        ThisDrawing.ActiveViewport = vportObj

        ' Create a circle using WCS coordinates
        Dim circleObj As AcadCircle
        Dim center() As Double = {1, 1, 0}
        Dim radius As Double = 0.5
        circleObj = ThisDrawing.ModelSpace.AddCircle(center, radius)

        ' Get the UCS transformation matrix
        Dim TransMatrix(,) As Double
        TransMatrix = CType(ucsObj.GetUCSMatrix(), Double(,))

        Dim str As String
        Dim r, c As Integer
        For r = 0 To UBound(TransMatrix, 1)
            For c = 0 To UBound(TransMatrix, 2)
                str = "r = " & CStr(r) & " c = " & CStr(c) & "   " & CStr(TransMatrix(r, c))

        ' Transform the circle to the UCS coordinates
        MsgBox("Transform the circle.", , "GetUCSMatrix Example")
        MsgBox("The circle is transformed.", , "GetUCSMatrix Example")
    End Sub

Autocad Object Types in Visual Studio

The basic object, then AcadObject, then AcadEntity, then the specialized specific top objects. This is the object inheritance hierarchy in autocad. Most objects contained by the document derive from the most general AcadObject. Hence most objects have those basic properties, which include Handle, ObjectName, ObjectID and OwnerID. All the graphic elements, things you can see, like lines and circles, further derive from the AcadEntity object. The quickest way to survey this in the ActiveX Reference Guide clicking on the graphical Object Model. The dwg document object itself does not inherit from AcadObject, but most of what it contains does.

TreeView and ListView controls in Visual Studio are a little changed in Visual Studio from VB6. TreeView no longer uses a key or a relationship parameter in the argument list. There is a tag property that takes the place of key. Building a hierarchy is done by saving the node to a variable, then using its Nodes.Add method to create a new node directly as a child node.

To connect TreeView click event to the ListView display, MS uses the tag property, illustrated in this link.

The handle property of all AcadObjects is a string. It is permanent from session to session. The ObjectID serves the same purpose, (perhaps it preceded the handle historically) but its a Long integer, and it may change from session to session. OwnerID is the long integer container object. There is no owner handle. Once you have the handle captured to a string, or the ID to a long integer, you convert the string or long to an object with a method from AcadDocument, HandleToObject(string) or ObjectIDToObject(long). I keep Option Strict On to be constantly reminded when I am trying to pass variables from one type to another.

HandleToObject returns a generic object. But if it has a handle, its an AcadObject. So it can be immediately converted to AcadObject. In general, non-graphic elements derive from AcadObject only, and graphic elements derive from both AcadObject and AcadEntity. Everything in the drawing including Blocks collection and ModelSpace derive from AcadObject.

To further identify what type of object you have, use VB method TypeName and TypeOf

Dim obj As Object
Dim acad_obj As AcadObject
obj = acadDoc.HandleToObject(strhandle)
acad_obj = CType(obj, AcadObject)


Will return the typename and

  Case TypeOf acad_obj Is AcadBlock
  blk = CType(obj, AcadBlock)
  Debug.Print(blk.Name & " " & blk.Count.ToString)

TypeOf will provide a boolean true or false to convert obj to its most specific type so you can use those methods, such as Name of the Block.

To populate a TreeView with an unknown number of levels, a recursive function is made with the parent node passed as one of the parameters.

If you are looking at a Block structure, the top level is a Block, and the embedded blocks are BlockReference.

ModelSpace and PaperSpace are blocks in the Blocks collection, they are also provided as shortcut properties of the Document, so they can be found either way, but usually using the property of the Document.

Button6 is the Block button on the form, it should say Blocks, it gets the entire Blocks collection. clicking on a node in the tree loads one level into ListView.

    Private Sub Button6_Click(sender As Object, e As EventArgs) Handles Button6.Click
        Dim acadblks As AcadBlocks
        Dim blk As AcadBlock
        acadblks = acadDoc.Blocks

        Dim strhandle As String
        strhandle = acadblks.Handle

        'start treeview

        Dim rootnode As TreeNode
        rootnode = New TreeNode(acadblks.ObjectName)
        rootnode.Tag = acadblks.Handle

        For Each blk In acadblks
            pop_tree(blk, rootnode)
    End Sub

ListViewByHandle is used to get the whole Blocks collection but its also called by a click event in the Tree to load the item selected. It has to have a fairly comprehensive Select Case to handle entities, blocks, blockrefs and block collection. I don’t try to do any levels in the ListView, so for instance, if you click on a block, it writes the block handle, name etc on the first line, then all the first level constituents under it.

Sub listview_by_handle(strhandle As String)
        Dim item As ListViewItem
        Dim obj As Object
        Dim acad_obj As AcadObject
        Dim blk As AcadBlock
        Dim blkref As AcadBlockReference
        Dim acadblks As AcadBlocks

        With ListView1
            .Size = New Size(850, 500)
            .Columns.Add("ObjectName", 180)
            .Columns.Add("TypeName", 180)
            .Columns.Add("ObjectID", 100)
            .Columns.Add("OwnerID", 100)
            .Columns.Add("Handle", 100)
            .Columns.Add("NAME", 180)
            .View = View.Details
        End With

        obj = acadDoc.HandleToObject(strhandle)
        ' Debug.Print(TypeName(obj))
        acad_obj = CType(obj, AcadObject)

        item = New ListViewItem(acad_obj.ObjectName)
        item.SubItems.Add(CType(acad_obj.ObjectID, String))
        item.SubItems.Add(CType(acad_obj.OwnerID, String))

        Select Case True
            Case TypeOf acad_obj Is AcadBlocks
                ' MsgBox("AcadBlocks")
                acadblks = acadDoc.Blocks

                For Each blk In acadblks
                    item = New ListViewItem(blk.ObjectName)
                    item.SubItems.Add(CType(blk.ObjectID, String))
                    item.SubItems.Add(CType(blk.OwnerID, String))

            Case TypeOf acad_obj Is AcadBlock
                blk = CType(obj, AcadBlock)
                Debug.Print(blk.Name & " " & blk.Count.ToString)


                For Each acad_obj In blk
                    item = New ListViewItem(acad_obj.ObjectName)
                    item.SubItems.Add(CType(acad_obj.ObjectID, String))
                    item.SubItems.Add(CType(acad_obj.OwnerID, String))
                    If TypeOf acad_obj Is AcadBlockReference Then
                        blkref = CType(acad_obj, AcadBlockReference)
                    End If

            Case TypeOf acad_obj Is AcadBlockReference
                blkref = CType(obj, AcadBlockReference)
                Debug.Print("blkreference " & blkref.Name)


            Case TypeOf acad_obj Is AcadEntity

        End Select
    End Sub

pop_tree is the recursive routine that makes the tree. It passes a block type parameter and the node that is parent to the block. It immediately adds a node for the block, then it surveys the block and if the constituent is a blockreference, it recurses using the new node as parent. if the constituent is not a block, it creates a new node and makes the entry. it uses the tag to save the handle so when the tree is clicked, the handle is retrieved, and the listview can be populated from the handle.

    Sub pop_tree(ByVal blk As AcadBlock, ByVal parentnode As TreeNode)
        Dim acad_ent As AcadEntity
        Dim anode As New TreeNode
        anode = parentnode.Nodes.Add(blk.Name)
        anode.Tag = blk.Handle

        For Each acad_ent In blk
            If TypeOf acad_ent Is AcadBlockReference Then
                Dim blkref As AcadBlockReference
                blkref = CType(acad_ent, AcadBlockReference)
                blk = acadDoc.Blocks.Item(blkref.Name)
                pop_tree(blk, anode)
                Dim newnode As New TreeNode
                newnode = anode.Nodes.Add(acad_ent.ObjectName)
                newnode.Tag = acad_ent.Handle
            End If
    End Sub

the treeview mouseclick event

    Private Sub TreeView1_NodeMouseClick(sender As Object, e As TreeNodeMouseClickEventArgs) Handles TreeView1.NodeMouseClick
        Dim newSelected As TreeNode = e.Node
        'Dim newSelected As TreeNode = TreeView1.SelectedNode
        Dim strhandle As String
        strhandle = CType(newSelected.Tag, String)
    End Sub
End Class

Autocad Document Object in Visual Studio

Moving up the object model tree, AcadApplication has a property Documents which returns a collection of the open Documents in Autocad. Clicking Application in the clickable Object Model, in the ActiveX Reference Guide, then Documents, leads us to the Documents property. There is some sample code, but no list of properties and methods, which is confusing if we are looking for the Documents object. Sometimes the Property returns an Object with the same name and sometimes the name is different. The property of AcadApplication called Documents returns an AcadDocuments object but you look it up as Documents. Go back to the object model and directly click on Documents, or navigate the tree view in the Objects section to Documents.

clickable object model

The Documents (plural) collection has the minimum number of methods and properties, it is almost the same list as the plain VBA Collection object.
DOCS.ADD starts a new drawing
DOCS.ITEM switches between dwgs already open.
DOCS.OPEN opens an existing drawing.
DOCS.COUNT returns number of open drawings.

Clicking on the ADD Method in the Documents Collection bumps you to the generic ADD page under Methods and you have to find the text that applies to the DOCS object.

ADD takes a name parameter that is the template file to use for a new drawing. if no path is used, it looks in the template folder. you can use either a DWT or a DWG file. if the file is not in the template folder you have to give a complete path.

OPEN is a method in both Documents and Document. It works about the same in each, but Documents has a ReadOnly option that Document does not have.

CLOSE also is a method in both DOCS and DOC. in DOCS it will close all. It has a flag to save changes.

ITEM finds open drawings by either index number or name. The name is the name on the drawing tab plus the extension.

Everything you need to open, create and save drawings is under DOCS or DOC.

the DOCUMENT (single) object is the gateway to all the autocad drawing objects. It has many more properties and methods, including SAVE for previously saved files and SAVEAS for new drawings. It also has a property SAVED to indicate unsaved changes.

Every object in autocad, both the drawing entities and the drawing properties, inherit a prototype object called AcadObject. The actual drawing entities further inherit an object called AcadEntity. In the Objects section of the ActiveX Reference Guide you can find these two prototype objects and their lists of methods and properties under IAcadObject and IAcadEntity. AcadObject has a brief list of methods. AcadEntity has an extensive list of properties and methods. There is also a list of the containers or collection names where these objects are kept – ModelSpace, PaperSpace, Block, SelectionSet and Group. Its the ADDxxx Methods of these primary collection objects that create the drawing. Click on ModelSpace in the object model to see them.

Autocad Application Object in Visual Studio

The root or the trunk of the autocad COM object model is the AcadApplication object. It has to be obtained first to get anything else. If Autocad is running and you know it is running it can be obtained with GetObject.

        Dim app As AcadApplication
        app = CType(GetObject(, "Autocad.Application.22"), AcadApplication)

Autocad.Application.22 is a version specific program ID. you can also use a version independent string, Autocad.Application, but if you have more than one autocad version loaded, it will load the last one used and saved, probably.

here is the Autocad Application object Help page.

the clickable object model is easier to use as a reference base.

I cannot tell that any updates have been made for a long time so the exact year does not matter.

the easiest reference of all and its exactly the same material are the ActiveX API documentation found locally in these folders:

%ProgramFiles%\Common Files\Autodesk Shared\acad_aag.chm
%ProgramFiles%\Common Files\Autodesk Shared\acadauto.chm

In Visual Studio with Strict Typing On I added the Ctype function to get rid of the error.

‘app = GetObject(, “Autocad.Application.22”)
‘Option Strict On disallows implicit conversions from ‘Object’ to ‘AcadApplication’

In autocad VBA help, the root object is “ThisDrawing.Application”. This is because ThisDrawing is provided by the implementation as always available. ThisDrawing is AcadApplication.ActiveDocument. You can run all the sample code without changing that part of it by declaring an AcadDocument variable called ThisDrawing.

        Dim ThisDrawing As AcadDocument
        ThisDrawing = app.ActiveDocument

The only subtle difference, is that your ThisDrawing will not automatically follow a shifting ActiveDocument. Its only current if the ActiveDocument is not changed.

In the clickable object model, objects are noted with an oval, collections are noted with a rectangle. To get to the drawing objects, the path is Application – Documents – Document. ActiveDocument is a property of the Application, a shortcut to the main drawing entities.

here is an Application test file where i demo’ed many of the application methods and properties. most of the actual code is pasted from the help, going thru the application page and clicking on the method or property. i commented out much of it after i ran it once.

 Sub App_Test1()

        Dim app As AcadApplication
        app = CType(GetObject(, "Autocad.Application.22"), AcadApplication)

        Dim ThisDrawing As AcadDocument
        ThisDrawing = app.ActiveDocument

        Debug.Print("AcadApplication Name: " & app.Name)
        Debug.Print("AcadApplication FullName: " & app.FullName)
        Debug.Print("AcadApplication Path: " & app.Path)
        Debug.Print("AcadApplication Version: " & app.Version)

        Debug.Print("AcadApplication HWND: " & app.HWND)
        Debug.Print("AcadApplication LocaleId: " & app.LocaleId)

        Debug.Print("AcadApplication Caption: " & app.Caption)
        Debug.Print("AcadApplication Height: " & app.Height)
        Debug.Print("AcadApplication Width: " & app.Width)
        Debug.Print("AcadApplication WindowLeft: " & app.WindowLeft)
        Debug.Print("AcadApplication WindowTop: " & app.WindowTop)
        Debug.Print("AcadApplication WindowState: " & app.WindowState)

        Debug.Print("AcadApplication Visible: " & app.Visible)

        'not New AutoCAD.AcadAcCmColor
        Dim col As AcadAcCmColor
        'col = app.GetInterfaceObject("AutoCAD.AcCmColor.22")
        'Option Strict On disallows implicit conversions from 'Object' to 'AcadAcCmColor'
        col = CType(app.GetInterfaceObject("AutoCAD.AcCmColor.22"), AcadAcCmColor)
        Debug.Print("GetInterfaceObject ColorMethod: " & col.ColorMethod.ToString())

        'Closes the drawing file And exits the AutoCAD application. 

        'Dim pt1() As Double
        'Dim pt2() As Double
        'pt1 = Pt(1.3, 7.8, 0)
        'pt2 = Pt(13.7, -2.6, 0)
        'app.ZoomWindow(pt1, pt2)

        'Dim scalefactor As Double
        ''help exampe - Dim scaletype As Integer
        'Dim scaletype As AcZoomScaleType
        'scalefactor = 2
        'scaletype = AcZoomScaleType.acZoomScaledRelative
        'app.ZoomScaled(scalefactor, AcZoomScaleType.acZoomScaledRelative)
        'app.ZoomScaled(scalefactor, scaletype)


        'Dim zcenter() As Double
        'Dim magnification As Double
        'zcenter = Pt(3, 3, 0)
        'magnification = 10
        'app.ZoomCenter(zcenter, magnification)


        '' This example uses MenuBar to obtain a reference to the AutoCAD File menu.
        '' It then creates a new menu item and inserts it at the bottom of the File menu.
        '' The menu item will be automatically removed when AutoCAD is restarted
        'Dim menu As AcadPopupMenu, newMenuItem As AcadPopupMenuItem
        'Dim openMacro As String
        '' Use MenuBar property to obtain reference to the AutoCAD File menu
        'Menu = ThisDrawing.Application.MenuBar.Item("&File")
        '' Add a menu item to the new menu and
        '' assign an Open macro (VBA equivalent of: "ESC ESC _open ")
        'openMacro = Chr(3) & Chr(3) & Chr(95) & "open" & Chr(32)
        '' Add a menu separator
        'Menu.AddSeparator(menu.Count + 1)
        '' Add new menu item to File menu
        'newMenuItem = menu.AddMenuItem(menu.Count + 1, "NEW MENU ITEM", openMacro)
        'MsgBox("A new menu item has been added to the File menu!")

        ' This example uses MenuGroups to obtain a reference to the AutoCAD main menu.
        ' It then creates a new menu called TestMenu and inserts a menu item
        ' into it. The menu is then displayed on the menu bar.
        ' The menu item will be automatically removed when AutoCAD is restarted
        'Dim currMenuGroup As AcadMenuGroup
        'Dim newMenu As AcadPopupMenu
        '' Use MenuGroups property to obtain reference to main AutoCAD menu
        'currMenuGroup = ThisDrawing.Application.MenuGroups.Item("ACAD")
        '' Create the new menu in this group
        'newMenu = currMenuGroup.Menus.Add("TestMenu")
        '' Add a menu item to the new menu and
        '' assign an Open macro (VBA equivalent of: "ESC ESC _open ")
        'openMacro = Chr(3) & Chr(3) & Chr(95) & "open" & Chr(32)
        'newMenuItem = newMenu.AddMenuItem(newMenu.Count + 1, "Open", openMacro)
        '' Display the menu on the menu bar
        'newMenu.InsertInMenuBar(ThisDrawing.Application.MenuBar.Count + 1)
        'MsgBox("A new menu has been added to the ACAD menu system!")

        ' This example returns the current setting of
        ' LogFilePath from the preferences object.
        Dim preferences As AcadPreferences
        preferences = ThisDrawing.Application.Preferences
        ' Retrieve the current LogFilePath value
        MsgBox("The current value for LogFilePath is " & preferences.Files.LogFilePath, , "Preferences Example")

        ' This example obtains a reference to the Documents collection
        ' and displays information about the loaded documents.
        Dim Document As AcadDocument
        Dim msg As String
        msg = vbCrLf

        '' Cycle through the Documents collection and retrieve the names
        '' of the loaded documents
        For Each Document In app.Documents
            msg = msg & Document.Name & vbCrLf

        '' Display loaded document information
        If app.Documents.Count > 0 Then
            MsgBox("The loaded documents are: " & msg)
            MsgBox("There are no loaded documents!")
        End If

        ' This example reads and displays the current window state of the AutoCAD application.
        Dim CurrentState As String = ""
        ' Use the "WindowState" variable to determine the window state of AutoCAD
        Select Case app.WindowState
            Case AcWindowState.acMin : CurrentState = "Minimized"
            Case AcWindowState.acMax : CurrentState = "Maximized"
            Case AcWindowState.acNorm : CurrentState = "Normal Size"
        End Select

        ' Display window state
        MsgBox("AutoCAD is now: " & CurrentState)

    End Sub

How Do I Access Autocad Com Objects From Visual Studio?

I have stopped ignoring visual studio. I am having no issues accessing Com ActiveX objects from VB.Net with nearly the same code that works in VBA. I am not sure what all the naysaying is about. Every thread i have ever seen on running Com objects from Visual Studio says, Don’t do it, It’s a Bad Idea.

It seems to work fine.

as time goes by I may update this, but at this point –

Right click References in Solution Explorer, Add Reference, add the autocad type library.
To get the Autocad namespace to work across all modules, double click My Project in Solution Explorer and add Autocad to Imported Namespaces.

Get a book or study online the differences in syntax. They are pretty minor. The code editor is much improved. Arrays are a little simplified. Indexing is a little different, no static type. There is no variant type, but there is an object type that microsoft uses to read and write arrays to excel spreadsheets. (microsoft has a good one page link to connect to excel com objects HERE) Autodesk has no such single page that i can find, but here are some useful links.

All methods to be capitalized. All arguments in parentheses. No set statements.
Sometimes you have to figure out how to get the namespace shortcuts to work. The error reporting in vs really helps.
The Try, Catch, End Try error system works more cleanly.
Many more tools to use with collections and array types.
There is a stricter approach to datatypes, a setting to relax it, but i have Option Strict On. this is where a book will help. 2012 books are cheap.

So far i have pasted in VBA code, made changes per notes above, and the same objects work with the same logic – lines, polylines, dimensions, blocks, ucs, text, selection sets.

Code below is for version 2018 (“Autocad.Application.22”)

Module Module_Connect_Acad

    Public acadApp As AcadApplication
    Public acaddoc As AcadDocument
    Public acadms As AcadModelSpace

    Public Sub Connect_acad()
        acadApp = Nothing
        Dim strProgId As String = "AutoCAD.Application.22"

        Try         '' Get a running instance of AutoCAD
            'acadApp = GetObject(, strProgId)
            acadApp = CType(GetObject(, strProgId), AcadApplication)

            Try     '' Create a new instance of AutoCAD
                acadApp = CType(CreateObject(strProgId), AcadApplication)

            Catch ex As Exception
                Exit Sub
            End Try
        End Try

        acadApp.Visible = True  '' Display the application
        ' MsgBox("Now running " & acadApp.Name & " version " & acadApp.Version)

        'load whatever globals needed based on acadapp
        acaddoc = acadApp.ActiveDocument
        acadms = acadApp.ActiveDocument.ModelSpace
    End Sub

End Module

Set Bom_Record = Bom_Records.Add (strPartID)

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)

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

    '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

Some Excel Query Tools for BOM Sheets

Some Query Tools for Excel Sheets

A sheet is a named table. One column has all unique values. We search the column for a particular value and return the row. If the table is called PART, if the primary key column is PART_ID, if the search value is “2020_SD1”, the equivalent SQL would return the row –

WHERE Part_ID=”2020_SD1”;

Maybe missing some table qualifiers or parentheses, but essentially.

In VBA the FIND method of the RANGE object returns a RANGE of the first cell found. The END property of the RANGE will return the row.

Function find_row(strtable As String, strfind As String) As Range
 'assume column1 is key index unique search column
 'return a single row as range
    Dim ws1 As Worksheet, rng As Range
    Set ws1 = ThisWorkbook.Sheets(strtable)
    Set rng = ws1.Range("A1")
    Set rng = ws1.Range(rng.Address, rng.End(xlDown).Address)
    Set rng = ws1.Range(rng.Address).find(strfind, LookIn:=xlValues, lookat:=xlWhole)
    Set rng = ws1.Range(rng.Address, rng.End(xlToRight).Address)
    Set find_row = rng
    'returns error if not found, cant use .address or .end of nothing
End Function

This returns a range. Anytime we have a range, we can load it into directly into an array. Excel VBA autosizes the array with 2 dimensions, rows and columns, even if there is only 1 row.

This table has 3 columns – the return range has 1 row.

Sub test()
    Dim rng As Range
    Dim strtable As String, strfind As String
    strtable = "PART"
    strfind = "2020_SD1"

    Set rng = find_row(strtable, strfind)
    Dim ar As Variant
    ar = rng
    MsgBox ar(1, 1) & ar(1, 2) & ar(1, 3)
End Sub

In the BOM project, we need to join two tables. We need to select all rows in COMPONENT that have the Assy_ID we want and join those to the rows in PART where COMPONENT.Comp_ID = PART.Part_ID to get a list of all sub-parts paired with their attributes from both tables.

I am not an SQL expert, but I got two different versions to work in an old copy of MS Access.
The difference is whether the join is called out in the FROM (preferred in new version SQL) or the WHERE clause.

Where COMPONENT.Assy_ID = “string”;

Where COMPONENT.Assy_ID = “string” and COMPONENT.Comp_ID = PART.Part_ID;

In SQL we would do it all in one query, but in VBA the code will be easier to write and read if we do it in steps.

First if we are to present the list of available assemblies, we need distinct values in the COMPONENT.Assy_ID column.

I found this code, modified it to my needs. It returns a zero index single dimension array. We are going to use arrays for the product of any search. Another great feature of an array is that you can load a listbox on a form simply by Listbox1.List= ar.

Function GetUniqueValues() As Variant
    Dim data As Variant
    Dim temp As Variant
    Dim obj As Object
    Dim i As Long
    Set obj = CreateObject("scripting.dictionary")
    'gets col A less header
    'you can even sometimes forget you are passing a range to a variant array
    data = return_col("COMPONENT", "A2")
    'excel sizes the array per the range
    'format data(1 to 50, 1 to 1)
    For i = 1 To UBound(data, 1)
        obj(data(i, 1) & "") = ""
    temp = obj.keys
    GetUniqueValues = temp
End Function

function return_column is just like find_row.

 Function return_col(str_sheet As String, str_cell As String) As Range
    Dim ws1 As Worksheet, rng As Range
    Set ws1 = ThisWorkbook.Sheets(str_sheet)
    Set rng = ws1.Range(str_cell)
    Set rng = ws1.Range(rng.Address, rng.End(xlDown).Address)
    Set return_col = rng
End Function

We have a list of unique COMPONENT.Assy_ID. One is chosen. The next step is to return an array of the COMPONENT table filtered for the rows where COMPONENT.Assy_ID = “string”

Here are our table definitions. Part_ID is a unique key. Assy_ID and Comp_ID are a composite key.


We want to SELECT all columns in COMPONENT for a particular Assy_ID and join them to PART where Comp_ID = Part_ID and show the remaining 2 columns from PART. First lets return the filtered Component table for Assy_ID.

There is a bit going on here, but the function still only does one thing. Basically I run the same loop twice, first to find out how many rows there are, to use that number to re-dimension the array, then run the loop again to save the data. It might not be art but it works fine. The first thing done is to return the entire table COMPONENT to an array for searching.

        'returns COMPONENT table as array filtered for rows with assy_id
Function select_component(assy_id As String) As Variant
        'WHERE Assy_ID = "string"
    Dim rows As Integer, cols As Integer
    Dim r As Integer
    Dim findnum As Integer
        'return entire table to array for searching
        'dim array (1 to rows, 1 to 3 cols)
    Dim arr As Variant
    arr = return_table("COMPONENT")
    rows = UBound(arr, 1)
    cols = UBound(arr, 2)
    'search column1 for assy_id string twice
    'first time to redimension array
    For r = 1 To rows
           If arr(r, 1) = assy_id Then
             findnum = findnum + 1
           End If
    Next r
    If findnum <> 0 Then
         Dim ar_result As Variant
         ReDim ar_result(1 To findnum, 1 To cols)
         Exit Function
        End If
     findnum = 0 'reset
         'this is structured for a 3 column table, not any table
     For r = 1 To rows
          If arr(r, 1) = assy_id Then
               findnum = findnum + 1
               ar_result(findnum, 1) = arr(r, 1)
               ar_result(findnum, 2) = arr(r, 2)
               ar_result(findnum, 3) = arr(r, 3)
          End If
     Next r
    select_component = ar_result
End Function

the function return_table. The conventions are – tables start on A1, the first row is column labels, anytime a continous range is returned, the returned type is range, anytime a non-continuous range is returned, the return type is array.

Function return_table(str_sheet As String) As Range
    'returns entire table to range
    Dim ws1 As Worksheet, rng As Range
    Set ws1 = ThisWorkbook.Sheets(str_sheet)
    Set rng = ws1.Range("A1")
    Set rng = rng.CurrentRegion
    Set rng = rng.Offset(1, 0).Resize(rng.rows.Count - 1)
    'takes out the label row
    Set return_table = rng
End Function

The next step is to make the join with all rows from component and desc and um from part.

       'returns array COMPONENT joined with PART for Assy_ID sub_parts
       '1 to rows, 1 to 5 cols
 Function join_comp_part(ar3 As Variant) As Variant
       'passed in table is Component filtered for Assy_ID
     Dim rng As Range
     Dim r As Integer, rows As Integer, cols As Integer
     rows = UBound(ar3, 1)
     cols = UBound(ar3, 2) 'we know is 3
     Dim ar_result As Variant
        '3 cols from COMP, 2 cols from PART
     ReDim ar_result(1 To rows, 1 To 5)
     Dim assy_id As String
     Dim comp_id As String
     Dim comp_qty As Integer
     Dim part_desc As String
     Dim part_um As String
     For r = 1 To rows
        assy_id = ar3(r, 1)
        comp_id = ar3(r, 2)
        comp_qty = ar3(r, 3)
            'WHERE Component.Comp_ID = Part.Part_ID
            'search PART table column1 for part_id, return row as range
        Set rng = find_row("PART", comp_id)
        part_um = rng.Cells(1, 3).Value
        part_desc = rng.Cells(1, 2).Value
            ar_result(r, 1) = assy_id
            ar_result(r, 2) = comp_id
            ar_result(r, 3) = comp_qty
            ar_result(r, 4) = part_desc
            ar_result(r, 5) = part_um
     Next r

    join_comp_part = ar_result
End Function

That brings us to a stopping point, because up til now, if you accept the basic table relation as a workable BOM structure, we have simply joined the tables. From here on though, everyone has different needs, and will want to write the next manipulation according to their on-dwg bom style, or formatted to paste into their ERP structure.

So lets recap.

We have two tables. One table is the main PART table. Its a list of every PART identification number in the system. For the excel version, its of course a subset. It has 3 columns, part number, description and unit of measure. In the real world, especially if the intent is to interface directly to ERP, there would be some additional foreign key columns to purchase, track cost, show inventory etc. It has no information about the part using or being used by assemblies.

The second table is the assembly information table I called COMPONENT. It has a double key, the assembly number and the component number, parent and child, the part and sub-part. Neither of these numbers is unique singly in this table but is unique taken together. Both numbers are found in the PART table. I could have called the table ASSEMBLY. A third column has the quantity needed for the assembly.

Pic here

The first main function select_component searches COMPONENT for an Assy_ID and returns the table just for those parts.

Pic here

The second function join_comp_part joins the two tables

Pic here

Thats where we are at. Some subs are called but not shown. I am re-writing code as i go, having the first prototype working, making some fairly major revisions hopefully to make the code simpler, less chunky, more modular. its not finished code that has been in production. it is subject to change. You might notice there is no error checking. My goal is to figure out how i want it to work, includng a form interface, then break it with bad data and add error checking. The next step makes the actual dwg bom with an assembly quantity, extended quantities for sub-parts and loops thru the list looking for sub-parts to the sub-parts. I will show my version of an indented bill of material.

The Fundamental Bill of Material Relation

From the designer’s point of view, the Bill of Material turns a sketch into a construction document. Its hard to generalize about BOMs, because of the variability of use. A BOM for a mass produced product is not like one for engineer-to-order/make-to-order. A BOM for a product small enough to be made and shipped in a box is not like one loaded in pieces and assembled on-site. A BOM might be an instruction for an experienced shop to build, or for an in-experienced customer to assemble. It might be a list of spare parts for replacement when the originals wear out. It might be a sales tool meant to inspire confidence – the design approved by the customer before the sale is made. The main customer of the drawing might be the designer who has a problem and uses the drawing to work it out. Next year he will have a similar problem and this drawing will be his prime source. it has to be complete and make sense. The BOM names and identifies the end product and the parts to get there.

Whatever the purpose and appearance of the drawing level BOM, however it is constructed, it is a report. It’s a view of the data in a Parts database. The view can change. The PART table it is based on all have similar features.

A formal database table, whether its in MS Access or your half million dollar ERP, is a collection of related entities. The items in a table are related by being all of one kind. A second kind of relation is the relationship between tables. Every table is named. The columns in a table are named. The rows are not named. There is a fixed number of columns, after the table design is complete, but a variable number of rows. Each row is a record. Every table has one column where the values are unique, no duplicates, no blanks, called the primary key. It provides the key to the record. Another column usually has the foreign key. It has the same kind of data as the primary key, but duplicates are allowed. Matching the primary key from one table and the foreign key of a second table is how tables are related.

Manufactured products are composed of assemblies, which are composed of sub-assemblies and parts. Every tracked item has to have a unique identifying number or name. These all go into one PARTS table. Assys, Sub-Assys, Made Parts and Purchased Parts are all distinct line items in the same PART table. One Assy can use many Parts, and one Part can be used in many Assys. That is called a Many to Many relationship. There is no limit in theory how many levels of Sub-Assys can be used in other Sub-Assys. This cannot be modeled in a single table.

The purpose of formal database structure is to make the storage and retrieval of data efficient, to eliminate duplication of data. The most common relation is between two tables of two different kind of things. When items of the same type in a single table have a relationship between themselves – called a Unary relationship – and it is a many-to-many type, a second table has to be created.

Modeling a relational dabase in Excel for output to Autocad – you can think of this as a pilot project, or a demonstration, or an educational project, but i think its a little bit more. Excel is the BOM autocad has always needed. Its a natural fit. Autocad never had a BOM solution and only introduced tables 2008 (or so). The designer creates the BOM, whether he does it in the window of the ERP program, or Excel, or with pencil and paper. Even if you hire Bill Gates to do your website, at some point he is going to ask you, where is your content? The ERP is the same way. You won’t get to standard parts if you implement with no knowledge of how a parts table works with no part naming conventions at all. Accounting may be satisfied, but engineering is just feeding the ERP beast and gaining nothing from it. With a mock up at least, you have to start thinking about how to name parts so it works over time. Excel can be used to paste into autocad and paste into ERP, so its not quite the same thing as duplicating data. It’s the most convenient editor. It has possible application for designing standard products, which are hard to do in an engineer to order company.

Here is the basic model of the Bill of Material relation – how you capture Assemblies of Sub-assemblies of Parts using database concepts. I will start doing the supporting code in the next post.

I had to make a decision about my on-dwg bom. Remember its just a report and is a view of the data. I chose 3 levels with 3 columns. I called them Assy_ID, Part_ID and LVL3. The part number is in the appropriate column. Thats my version of an indented bill. Google “Indented Bill of Material” for other ideas. So i do not have an infinite recursion of parts here. You could do that, but it would require a LEVEL column with an integer. Those dont seem particularly easy to read. As a first attempt, this works.

I have to credit my text. The E-R diagram and table structure (and probably some of the verbiage) are from
Modern Database Management, Hoffer, Prescott, McFadden, Prentice Hall, Sixth Ed, 2002

Equations of Lines in Space

In 2D coordinates, AX + BY + C = 0 is the equation of a line. In 3D coordinates, this equation represents a plane. In 3D coordinates its not possible to specify a line by a single equation. A 3D line is represented by 3 equations, one for each x,y and z.

A straight line in space is completely determined by two points. It is also completely determined with a single point and a set of direction numbers. The direction numbers are the difference of the coordinates over any segment. Direction numbers come in sets of three. Its the same idea as a vector. A line has an infinite amount of direction number sets, all of them proportional. Just as you can multiply a vector by a scalar, and obtain another vector parallel but with a different length, you can multiply direction numbers by a scalar, call it t, and obtain further points on the same line.

If two points are given, (x1,y1,z1) and (x2,y2,z2), direction numbers are (x2-x1, y2-y1, z2-z1). These are often referred to as (a,b,c).

Either point can be used for the given point

X = x1 + at
Y = y1 + bt
Z = z1 + ct

To create an unending line in both directions, t takes on values between negative and positive infinity.

if we take an example, let one point P1 be the origin and the other point be (2,3,4). use P1 for the given point and (2,3,4) for (a,b,c)

X = 2t
Y = 3t
Z = 4t

the length of a line segment is the sq root of the sum of the coordinate deltas squared, so Len (2,3,4) is 29^1/2

Len = \sqrt{x^2 + y^2 + z^2} = \sqrt{4 + 9 +16} = \sqrt{29}

when t = 0, the point on the line is the origin. When t=1, the point is (2,3,4).

Len = \sqrt{29}

t = 1

so if

Len = 1

t = \frac{1} {\sqrt{29}}

when the line segment is 1, the coordinates are the direction cosines. the angle between the line and each of the coordinate axes can be found by taking the ArcCosine.

to put an arrowhead on a 3D vector, i insert an arrowhead block. I wanted to draw and do a revolve, but i was not initially able to find the activex method for revolve, so insert a block is the standby. i drew the cone shape the same size as dimension arrowheads so the scale factor works in a similar way. the insertion point is the head of the vector. we need the angle. i pass a direction vector, which is a parallel vector any length. normally i will just pass the same vector, but sometimes like when constructing an XYZ axes it is just as convenient to pass a unit vector.

Sub arr(pt1() As Double, D() As Double)
‘3D arrow
‘pt1 is location of the arrow
‘D is direction vector

the direction cosines of the direction vector are the familiar array of 3 doubles.

Dim dir_cos() As Double ‘direction cosines
dir_cos = ret_3D_angle(D)

the function ret_3D_angle takes one parameter, the direction vector, calculates the length, divides the x,y,z values by the length, and returns all 3 together in an array, just like a point.

the direction cosine for the x axis – the x coordinate for the direction vector at the place where the length is one – is used and the angle found for autocad to use later.

Dim alpha As Double
alpha = WorksheetFunction.Acos(dir_cos(0))

we have not passed in the actual tail coordinate of the vector we are trying to arrow. we have passed in the head coordinate and a vector parallel. the direction vector is positionless. its just 3 numbers. if we subtract those from the head coordinates, we will have a second point on the vector.

when we do that we have enough coordinates to change the user coordinate system, ucs in autocad, to a plane defined by the two lines, the vector itself and a line from the tail point just calculated parallel to the x-axis.

First we insert the arrowhead at rotation zero at the world coordinate system. then change the user coordinate system to the one defined by our vector and a line parallel to the x-axis. when you rotate an object, it rotates around a line perpendicular to the user coordinate system. so in effect by changing the user coordinate system, we have already made one rotation, even though we have not applied it yet. now rotate the arrowhead by the insertion point through alpha radians that we previously calculated. that completes the 3D arrow rotation.

Even though there are 3 angles from a vector to each of the axes, any two of them determine the third.

here is a diagram i did a while back on the arrowhead rotation problem.

x1,y1, z1 is any valid point on the vector which we have found above. to create a new ucs, a new origin is located, then a point on the new x-axis, and a point on the new y-axis. they must form a right angle. the new origin can be assembled from the head and tail coordinates. the new x-axis point can just add one value for x, and the new y-axis can use the head coordinates. some special error checking has to occur when the arrow is on the x-axis.

Sub arr(pt1() As Double, D() As Double)
'3D arrow
'pt1 is location of the arrow
'D is direction vector
    Dim x1 As Double, y1 As Double, z1 As Double
    Dim x2 As Double, y2 As Double, z2 As Double
    Dim origin() As Double, xAxis() As Double, yAxis() As Double
    Dim dir_cos() As Double  'direction cosines
    dir_cos = ret_3D_angle(D)
    Dim alpha As Double
    alpha = WorksheetFunction.Acos(dir_cos(0))
   Dim blkref As AcadBlockReference
   Dim blkname As String
   blkname = "Ar_Head3D"
   If sc = 0 Then sc = 1
   'need an illustration
   'pt1 is the location for the arrowhead
   'D is the direction vector
   'the new origin is x from pt1 and y and z calculated from tail of D
   'transfer pt1 to x2,y2,z2
   x2 = pt1(0)
   y2 = pt1(1)
   z2 = pt1(2)
   ' direction vector is positionless
   ' so we in effect put it at head and find tail
   x1 = x2 - D(0)
   y1 = y2 - D(1)
   z1 = z2 - D(2)
   ' the new origin and new xaxis can be calculated
   ' the new yaxis is the tip of the arrow
   origin = pt(x2, y1, z1)
   xAxis = pt(x2 + 1, y1, z1)
   yAxis = pt1
   set_wcs  ' make sure we insert at world ucs

   Set blkref = acadDoc.ModelSpace.InsertBlock(pt1, blkname, sc, sc, sc, 0)
   'error when d(1) = 0 ucs yaxis is same as origin
   'when alpha = 0 dont need to rotate
   'when alpha = pi dont need to change ucs
   'On Error Resume Next
   If dir_cos(0) = 1 Then Exit Sub
   If dir_cos(0) <> -1 Then
        Call set_ucs(origin, xAxis, yAxis, "UCS_alpha")
        End If
        blkref.Rotate pt1, alpha
End Sub

Function ret_3D_angle(D() As Double) As Double()
'D is direction vector
'returns an array of 3 doubles that contain the direction cosines
Dim vector_len As Double
Dim pt1() As Double
Dim A As Double, B As Double, C As Double

vector_len = leng(D)

If vector_len = 0 Then
MsgBox "zero vector in ret_3D_angle"
Exit Function
End If

A = D(0) / vector_len
B = D(1) / vector_len
C = D(2) / vector_len

pt1 = pt(A, B, C)
ret_3D_angle = pt1

End Function

Sub set_ucs(origin() As Double, xAxis() As Double, yAxis() As Double, strName As String)
    Dim ucsObj As AcadUCS
    Set ucsObj = acadDoc.UserCoordinateSystems.Add(origin, xAxis, yAxis, strName)
    acadDoc.ActiveUCS = ucsObj
 End Sub
    Sub set_wcs()
     ' Call Connect_Acad
    Dim ucsObj As AcadUCS
    Dim pt0() As Double, ptx() As Double, pty() As Double
    pt0 = pt(0, 0, 0)
    ptx = pt(1, 0, 0)
    pty = pt(0, 1, 0)

   Set ucsObj = acadDoc.UserCoordinateSystems.Add(pt0, ptx, pty, "World")
   acadDoc.ActiveUCS = ucsObj
 End Sub