topic How to run faster autocad-excel in VBA Forum

How to run faster autocad-excel

dancalgary — Fri, 20 Feb 2009 18:55:07 GMT

I have done a lot of Autocad vba routines that interact with excel. I want to be able to make them run faster. I understood that is possible to create a database and to run my queries through this database from autocad.
Can somebody help me with an example
Thank you

Re: How to run faster autocad-excel

HJohn1 — Fri, 20 Feb 2009 20:57:48 GMT

{code}routines that interact with excel. I want to be able to make them run faster.{code}
I would go with VSTO and AutoCAD .NET. For database I (personally, there might be other better choices) would use SQL Server Express (Free version). There are a lot of sample on the topic on the web. Good luck.

Re: How to run faster autocad-excel

Anonymous — Sat, 21 Feb 2009 10:59:45 GMT

My personal experience is to take a good, tough look at the code. Most of the times i see VB being (markedly) slower is because VB *lets* you write sloppy stuff.

First places Id look:

1) Excel: is your excel code macros that were recorded, with a few variables thrown in to replace the 'hard' vales put in while recording? Thats a no-no. Macro recorders try and emulate what a user would do using the keyboard/mouse. And thats usually no more efficient. Dont "select" cells, assign them to a range object. Turn off screen updates. Remember, your code is running like there is NO screen visible; you are working with your eyes closed.

2) Loops: Move everything you can out of the loop. I dont care what loop it is, if you are setting values that dont need to be set, it slows you right down.

3) Variants: Toss em wherever possible, use strict typing of your variables and avoid conversions wherever possible.

4) OLE: Early Binding. Its faster to develop this way, and runs a smite faster on running.

5) Compiling: Its longer to develop, but faster to run, if you take your VBA and make it VB instead.

6) Apparent speed: Splash screens werent invented 'cause they look cool, they take the users mind off the fact that something else is loading. Check for data validity when the text box loses focus. Start Excel invisibly while the user is doing something else in Acad. Your app is probably sitting idle at the start, waiting for something to do... and then trying to do everything after the user clicks a button - get done, what can be done, up front. Its also a good way to check for errors and such before you get stuck in some long, slow loop.

Re: How to run faster autocad-excel

Anonymous — Sat, 21 Feb 2009 20:21:31 GMT

Hi rocheey,

While most of the advise you gave is good, some items might be seen from
a different viewpoint.

"Most of the times i see VB being (markedly) slower is because VB *lets*
you write sloppy stuff."

So what is different from all other programming languages? You can
write sloppy code in any language.

"5) Compiling: Its longer to develop, but faster to run, if you take
your VBA and make it VB instead."

Only if you compile it to a DLL then reference it and call it from the
VBA program. Because an EXE file runs "Out of Process" in the AutoCAD
environment, it runs slower than it would as VBA code.

"Check for data validity when the text box loses focus"

While not unncessary, it's better to check the input as the user makes
it and reject invalid input to a control before exiting the control.
The user will never notice this happening, whereas a rejection on
exiting the control always ends up being inconvenient to the user.

Regards

Laurie Comerford

rocheey wrote:
> My personal experience is to take a good, tough look at the code. Most
> of the times i see VB being (markedly) slower is because VB *lets* you
> write sloppy stuff. First places Id look: 1) Excel: is your excel code
> macros that were recorded, with a few variables thrown in to replace the
> 'hard' vales put in while recording? Thats a no-no. Macro recorders try
> and emulate what a user would do using the keyboard/mouse. And thats
> usually no more efficient. Dont "select" cells, assign them to a range
> object. Turn off screen updates. Remember, your code is running like
> there is NO screen visible; you are working with your eyes closed. 2)
> Loops: Move everything you can out of the loop. I dont care what loop it
> is, if you are setting values that dont need to be set, it slows you
> right down. 3) Variants: Toss em wherever possible, use strict typing of
> your variables and avoid conversions wherever possible. 4) OLE: Early
> Binding. Its faster to develop this way, and runs a smite faster on
> running. 5) Compiling: Its longer to develop, but faster to run, if you
> take your VBA and make it VB instead. 6) Apparent speed: Splash screens
> werent invented 'cause they look cool, they take the users mind off the
> fact that something else is loading. Check for data validity when the
> text box loses focus. Start Excel invisibly while the user is doing
> something else in Acad. Your app is probably sitting idle at the start,
> waiting for something to do... and then trying to do everything after
> the user clicks a button - get done, what can be done, up front. Its
> also a good way to check for errors and such before you get stuck in
> some long, slow loop.

Re: How to run faster autocad-excel

Anonymous — Thu, 26 Feb 2009 00:37:25 GMT

I have a few quick questions about Excel and running it in the background and what not.

Here is my code it is the first attempt ot success at calling Excel from Autocad 🙂

Private Sub UserForm_Initialize()

'Read data in from EXCEL sheet

On Error Resume Next
Dim ExcelApp As Excel.Application
Dim tmp As String
Dim i As Integer
Dim Workbook As Excel.Workbook
Dim WorkSheet As Excel.WorkSheet

Set ExcelApp = GetObject(, "Excel.Application")

If Err Then
Err.Clear
Set ExcelApp = CreateObject("Excel.Application")
If Err Then
MsgBox "Cannot start Excel", vbCritical, "Error Starting Excel"
Unload Me
End If
End If
ExcelApp.Visible = True

Set Workbook = ExcelApp.Workbooks.Open("C:\clients.xls")
Set WorkSheet = ExcelApp.Worksheets(1)

ExcelApp.Parent.Windows(1).Visible = False

With dataListBox
i = 1
tmp = WorkSheet.Cells(i, 1).Value
Do While tmp <> ""
.AddItem WorkSheet.Cells(i, 1).Value
i = i + 1
tmp = WorkSheet.Cells(i, 1).Value
Loop
End With
End Sub

How might I be able to make that code better?
Here is the things I would like to change about it from my point of view.
1. I dont want to screw around with any documents that Excel may have open, such as saving them or closing Excel or anything like that, I do need to close out of the work book but I thought I would put that under the Cancel button action or after I am done doing my work with excel.
2. Autocad/Macro/Dialog box to keep focus and not Excel.

Hope to hear back from you guys and I love reading this form it helps out a lot.

--Zac