How to make excel vba crash proof?

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

Write a routine that will open a new Text file, "Project Log & [TimeStamp].txt"

Code:

---

'Module level Variable
Dim LogFile as Object

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

---

Quote:

---

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

Hi Sam,

Thanks for the reply. I will try it :)

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?

Hi Sam, i think i know what causes the crash.

I use this code to link excel with autocad

Code:

---

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


Dim warning As Integer


'ENSURES USER KNOWS OPEN DRAWING WILL BE EDITED, YES NO TO PROCEED
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 NEXT PROCEDURE TO RUN, SPECIFYING HOW MANY RECORDS HAVE BEEN SAVED
'Call Start_Drawing(int_)


End Sub
Sub Talk_CAD()
'SUB PROCEDURE, REQUIRES INPUT STRING TO RUN AND GIVES SPECIFIC COMMAND TO AUTOCAD
    '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? :)

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,
OR
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

Code:

---

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

Yes, the 'allows access to autocadPublic acadApp As Object is in the vba.
And what about the option explicit? should i use it?

Quote:

---

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?

'allows access to autocadPublic acadApp As Object
Should be two lines

Code:

---

'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
and
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.

Oh, i guess that's because of the pasting.
It's two lines in my code.

btw, i found this

Code:

---

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

Thanks