VBA vs pyautocad speed when communicating with autocad and excel

Question

I have created a program that gets objects from autocad (e.g. line, blocks) then analyze the objects using VBA then write the results of the analysis in excel workbooks. I use VBA because it's available in excel. But the speed of communication from VBA to excel and VBA to autocad has always been slow. I am looking at using pyautocad. Before I delve in to a new language, I want have some advise if pyautocad is faster than VBA, expecially when communicating with autocad and excel.

I have pasted a very small part of the code below just so you have an idea of how I use autocad and excel. I have the same procedure for several other objects. The problem is this process takes a lot of time when I do this to all the object I need from autocad.

Sample code of read from Autocad and write to excel:

'-----------------Select all lines------------
'set variables to be used
Set MySelection = acadDoc.SelectionSets.Add("MySelection")
FilterType(0) = 0: FilterData(0) = "line"   'object type
FilterType(1) = 8: FilterData(1) = "MyLayer"   'layer name

'select all the lines in acad
MySelection.Select acSelectionSetAll, , , FilterType, FilterData
'---------------

LineWB.Worksheets(CurrentWorkSheet).Range("A1") = MySelection.Count
'---iterate through the selection and update or make pipes in the database-----
For x = 0 To MySelection.Count - 1

    Set AcadLineVar = MySelection.Item(x)

    CellVar = "A" & x + 5
    LineWB.Worksheets(CurrentWorkSheet).Range(CellVar) = AcadLineVar.StartPoint(0)
    CellVar = "B" & x + 5
    LineWB.Worksheets(CurrentWorkSheet).Range(CellVar) = AcadLineVar.StartPoint(1)
    CellVar = "C" & x + 5
    LineWB.Worksheets(CurrentWorkSheet).Range(CellVar) = AcadLineVar.StartPoint(2)
    CellVar = "D" & x + 5
    LineWB.Worksheets(CurrentWorkSheet).Range(CellVar) = AcadLineVar.EndPoint(0)
    CellVar = "E" & x + 5
    LineWB.Worksheets(CurrentWorkSheet).Range(CellVar) = AcadLineVar.EndPoint(1)
    CellVar = "F" & x + 5
    LineWB.Worksheets(CurrentWorkSheet).Range(CellVar) = AcadLineVar.EndPoint(2)

Next x
'-----------

Sample code of read from excel to autocad:

'Get Properties
InterfacePropVar = BlocksWB.Worksheets("Interface").Range("D5:M100000")    


'-----For interface tags-----
NumberOfInterface = BlocksWB.Worksheets("Interface").Range("A1")
For a = 1 To NumberOfInterface 

    PtS(0) = InterfacePropVar(a, 1) + 5
    PtS(1) = InterfacePropVar(a, 2) + 5
    PtS(2) = InterfacePropVar(a, 3) 

    Name = InterfacePropVar(a, 4) 


    Set TextVar = acadDoc.ModelSpace.AddText(Name, PtS, 3)
    TextVar.Layer = "InterfaceTagLayer"
    TextVar.Color = acRed
    '-----
Next a
'-----

Would you please post some or all of the code that you used so that StackOverflow community members can see if there is any low-hanging fruit in making it go faster? — elliot svensson, Mar 20 '18 at 21:52
I have added some sample code. I believe the problem is with the com interop. — Floben Dale Moro, Mar 21 '18 at 12:30

score 0 · Answer 1 · answered Mar 20 '18 at 21:51

I ran a macro between AutoCAD and Excel and found that all the major time-consuming things are when VBA reads and writes to/from AutoCAD and Excel MORE TIMES than is absolutely necessary. If you can do all the loading and unloading in massive single steps, that really saves time. I would personally take this approach first before switching languages, due to the user's (your) own learning curve. But then again if you wanted to learn Python, maybe this is your chance...

I agree. I have read that this is because of the COM interop. I'm going for .net framework. — Floben Dale Moro, Mar 21 '18 at 12:24

VBA vs pyautocad speed when communicating with autocad and excel

1 Answers1