View Full Version : How to make excel vba crash proof?

09-26-2016, 10:27 AM
Hi all,:)

I have vba to copy selection and paste it after concatenated with some specific string into a notepad and then save it. And then it will open autocad and write a command then run my lisp to plot it into the autocad drawing.
Itís run the way I want it. However it will occasionally crash the excel. The excel windows will close itself and then opened again in recovered mode. Iím not sure as to what caused the crash. Can you guys please suggest me a method to analyse what causing the crash and how to fix it?
I attach my vba and my lisp

Thank you very much :yes

09-26-2016, 12:55 PM
Write a routine that will open a new Text file, "Project Log & [TimeStamp].txt"

'Module level Variable
Dim LogFile as Object

Sub WriteLog(Msg As String)
'This sub saves Project Log & TimeStamp.txt every time
End Sub

If LogFile is Nothing then open new log file
Append Msg to log
Save log

In all the other subs in your project, add the first line
WriteLog "Opening Sub Name"

At the end of all subs
WriteLog "Ending Sub Name"

Just before Exiting any Sub
WriteLog "Exiting Sub Name"

After Excel crashes a couple of times, you will have a good idea where to issue is. Then you can add the WriteLog calls inside the subs as desired. The Key is that WriteLog saves the Log before Excel Crashes. The issue is after the last entry in Project Log & TimeStamp.txt

09-26-2016, 01:27 PM
Hi Sam,

Thanks for the reply. I will try it :)

09-27-2016, 02:16 AM
Hi Sam. As i understand, this sub is only to open a text file. What should i tell the sub what to write in the txt that is opened by the sub?

09-27-2016, 04:43 AM
Hi Sam, i think i know what causes the crash.

I use this code to link excel with autocad

'allows access to autocadPublic acadApp As Object
Public acaddoc As Object
Sub open_Cad()

Dim warning As Integer

warning = MsgBox("Coordinates will be saved into currently opened drawing." & vbCrLf & "If no drawing is open, a blank drawing will be opened." & vbCrLf & "Would you like to continue?", vbYesNo, "Data Loss Warning")

Select Case warning
'if they select yes to proceed
Case 6
On Error Resume Next
Set acadApp = GetObject(, "AutoCAD.Application")

'If AutoCAD is not opened create a new instance and make it visible.
If acadApp Is Nothing Then
Set acadApp = CreateObject("AutoCAD.Application")
acadApp.Visible = True
End If

'Check (again) if there is an AutoCAD object.
If acadApp Is Nothing Then
MsgBox "Sorry, it was impossible to start AutoCAD!", vbCritical, "AutoCAD Error"
Exit Sub
End If
On Error GoTo 0

'If there is no active drawing create a new one.
On Error Resume Next
Set acaddoc = acadApp.ActiveDocument
If acaddoc Is Nothing Then
Set acaddoc = acadApp.Documents.Add
End If
On Error GoTo 0

'Check if the active space is paper space and change it to model space if so
'If acaddoc.ActiveSpace = acPaperSpace Then
'acaddoc.ActiveSpace = acModelSpace
'End If
Case 7
Exit Sub
End Select

'acadApp.ActiveDocument.SendCommand ("wew ")
'Call Start_Drawing(int_)

End Sub
Sub Talk_CAD()
'acadApp.ActiveDocument.SendCommand ("-layer set AS_SURVEYED " & vbCrLf & "-color BYLAYER ")
'acadApp.ActiveDocument.SendCommand ("circle 50,50 50 ")
acadApp.ActiveDocument.SendCommand ("wew ")
End Sub

Whenever i click on "File", or "Window", or "Edit" on the autocad toolbar. It will freeze for a second and then the excel will close itself and then open again in auto-saved mode. I took the code from the internet. I don't know what's causing the crash. Do you have any idea why? :)

09-27-2016, 05:20 AM
Look at the first line in the code in your last post
'allows access to autocadPublic acadApp As Object

Is that correct as it is in your VBA,
is it an artifact of pasting the code into the VBAX Forum editor?
If you're already using "Option Explicit," well, never mind, the answer is obvious.

Something to try instead of

Set acadApp = CreateObject("AutoCAD.Application") Is to open Autocad external to Excel with a DOS command, wait for it to open, then us GetObject again

09-27-2016, 05:52 AM
Yes, the 'allows access to autocadPublic acadApp As Object is in the vba.
And what about the option explicit? should i use it?

Is to open Autocad external to Excel with a DOS command, wait for it to open, then us GetObject again
That is what i'm lookin for. wait to open then getobject again. how do you do that?

09-27-2016, 06:38 AM
'allows access to autocadPublic acadApp As Object
Should be two lines

'allows access to autocad
Public acadApp As Object
Just that might solve the issue. :dunno:

Option Explicit at the top of your code page requires explicit declaration of all variables, AND would have told you that acadApp was not declared.

You can manually place Option Explicit at the top of all existing Code pages
In the VBA Menu, Tools>> Options >>Editor Tab, Check all the options in the Code Settings Frame. On the General tab, Check Break on All Errors and Background Compile.

The Require Variable Declaration will insert Option Explicit into new Code Pages for you.

09-27-2016, 01:18 PM
Oh, i guess that's because of the pasting.
It's two lines in my code.

btw, i found this

Sub Main()
Dim ACAD As AcadApplication 'Create ACAD variable of type Acad Application

On Error Resume Next 'This tells VBA to ignore errors
Set ACAD = GetObject(, "AutoCAD.Application") 'Get a running instance of the class AutoCAD.Application
On Error GoTo 0 'This tells VBA to go back to NOT ignoring errors

If ACAD Is Nothing Then 'Check to see if the above worked
Set ACAD = New AcadApplication 'Set the ACAD variable to equal a new instance of AutoCAD
ACAD.Visible = True 'Once loaded, set AutoCADģ to be visible
End If

ACAD.ActiveDocument.Utility.Prompt "Hello from Excel!" 'Print a message to the AutoCADģ command line
End Sub

and using the code to open the cad.
and by activating the autocad library in the vba, It's not crashing anymore now