Run DVB procedure with parameters from Excel

[miguelmachadoecosta]

I have a somewhat complex independent project for AutoCAD in a dvb file.

Now I'm working on something in Excel and I would like to use some of the procedures of the AutoCAD project. In order to avoid copying all those procedures to Excel, is there a way of running them from Excel with VBA or VB.NET? These procedures have input parameters.

Thanks in advance.

[miguelmachadoecosta]

Am I wrong to suspect that the only way to avoid duplicating code is to have all the common procedures in a dll assembly (in this case in vb.net), so that they can be used by both AutoCAD and Excel? At least I cannot find a way to do it with VBA.

[norman.yuan · Accepted solution]

Firstly, I am not a fan of automating AutoCAD from another application (in your case, I guess, it is user uses Excel's VBA to control AutoCAD). If the Excel is mainly used to provide data from AutoCAD to run, it would be much better to let user ONLY run AutoCAD, which access data in Excel sheet (which, in turn, may not even need to have Excel running), so user only deals/faces/uses one application. Not to mention the huge speed difference.

Anyway, if you need to make AutoCAD to run some VBA macros from external application, you can only call LoadDVB() (if the DVB file is not loaded on startup) and then call RunMacro(). And as you know VBA macro does not take parameters.

Obviously, the the VBA macro is supposed to do something in AutoCAD according to some data, the macro itself needs to get input somehow during its execution (showing userform for input, reading data from DB/file...). Of course you may have other "smaller" macros, which is called by other Macros and expects some needed data available when being called.

Back to your question, a simple way to pass data/paramters from outside app into AutoCAD and allow VBA macro to pick them up would be to use AutoCAD's user variables (USERSx, USERRx, USERIx), like this:

In your external App (Excel VBA)

Dim cadApp As AcadApplication

Dim dwg As AcadDocument

set cadApp=....

set dwg=cadApp.ActiveDocument '' or  cadApp.Documents.Open(....)

Dim origVal As String

origVal=dwg.GetVariable("UserS1")

dwg.SetVariable "UserS1","MyTextValue" 

cadApp.LoadDVB "........."

cadApp.RunMacro() '' This macro would need to read data from "UserS1" first, as input

dwg.SetVariable "UserS1", origVal 

Then in the AutoCAD VBA, you woudl have a macro:

Public Sub DoWork()

   Dim param As Striing

    param=ThisDrawing.GetVariable("UserS1")

    '' Do real work based on the input

End Sub

If the amount of input data is huge, or is quite complicated, you could let your external all write the data into a file (Text/Xml), before let AutoCAD run its macro. Then the the AutoCAD macro, it would read the data from the file first and do AutoCAD business based on the data.

Writing DLLs that can be shared/references by both AutoCAD VBA and Excel VBA would make data transfer between easier. But it would not change the fact the 2 applications must communicate between 2 separated proccesses, thus very slow. Also, since both VBA use COM APIs, VB.NET (or current Visual Studio) is for .NET, the DLLs created MUST be exposed as COM, which make things unnecessarily complicated.

In most cases, if I were you, I would focus the application on AutoCAD side, using AutoCAD .NET API and not waste time on AutoCAD VBA (then, I do not know much what situation you are in).

[miguelmachadoecosta]

Thank you for your very complete analysis, that fully answers my question.

Although it would not be impossible to execute this operation from AutoCAD it made more sense to me doing it from Excel because:

1) creating a new drawing is only one of many operations that will be available

2) besides input in the Excel's workbook there is also interaction with a third program, which provides more data

3) all the other operations don't involve AutoCAD (only Excel and the third program) and part of the procedures that are needed to process the data for the drawing are also used for example to update a chart in Excel.

So it made more sense to centralize everything in Excel, which serves as the main tool and as "bridge" between the three programs.

I've already used AutoCAD's user variables for other purpuses in my other AutoCAD independent application, but in this case the limitation is the amount of data to be passed (a drawing will be created from scratch). The idea of using a temporary text file is a good one to try since it might be faster than storing and accessing the all the data in the workbook (which I'm not doing at the moment). It's one more option to consider.

Also in my other AutoCAD independent application I'm already using a DLL, which is exposed as COM, for some secundary operations as for example interaction with Google Drive, which does not support VBA. But for the moment my knowlegde of VB.NET is much more limited that VBA.

From what I've already implemented so far it seems that for this operation of creating a new small drawing from Excel it takes more time to open AutoCAD than the operations of creating objects themselves. In total no more than a few seconds, so maybe in this case I'll stick with Excel for now, first by duplicating the code and secondly by gradually, procedure by procedure, converting it to VB.NET. It seems the most viable solution. The only problem is the complexity of AutoCAD's .NET API compared to VBA. I once tried to manage xdata with VB.NET but ended up returning to a VBA workaround because it was much easier.

One small question to give me a start:

What would be the VB.NET equivalent of defining commands in a acaddoc.lsp file for running a procedure?

(defun c:<comand> ()
  (command "-VBARUN" "XXX.dvb!Module1.YYY" "USERVAR1" "USERVAR2")
  (princ))

Thanks again for the insight.

[norman.yuan]

---

One small question to give me a start:

What would be the VB.NET equivalent of defining commands in a acaddoc.lsp file for running a procedure?

(defun c:<comand> ()
  (command "-VBARUN" "XXX.dvb!Module1.YYY" "USERVAR1" "USERVAR2")
  (princ))

Thanks again for the insight.

---

When you say VB.NET, I assume you are actually referring to AutoCAD .NET API, which one can use any .NET language to work with, whilst VB.NET (and other .NET languages) can also use AutoCAD COM API.

With AutoCAD .NET API, we can define AutoCAD command with class CommandClassAttribute, which is sort of the equivalent to LISP's (defun C:XXXXX...), and the command can be entered at AutoCAD's command line, called in List routine, or called via COM API's AcadDocument.SendCommand(), just as regular AutoCAD commands.