Excel just dies - XL97

[dranon]

I have a button on the first of 30 worksheets which does a lot of work
behind the scenes: deleting worksheets, populating cells, creating new
worksheets.

Most of the time, but not frequently enough, it runs flawlessly.

But every once in a while it will start the process, delete all the
worksheets it is supposed to delete, create a lot of the sheets it is
supposed to create and then, near the end of the process, before it
has completed 100% of the sheets it is supposed to create, it just
stops dead in its tracks. In order to recover I have to go into VBA
(alt-11) and enter a "application.screenupdating = true" command into
the immediate window.

However, while the above gives me access to my excel workbooks again,
the button will now continually fail until I close all open workbooks,
completely exit excel and fire everything back up again.

When I say it fails, it fails the same way as the first failure: it
deletes and creates most of the worksheets and then it just dies. I
didn't mean to say that the button doesn't do anything. It does. And
all my message boxes run properly, too.

Since I routinely have 10 or more workbooks open at once, I'd really
like to figure out a way for me to be able to run the routines
associated with the command button again without having to restart
Excel.

Yes, I have tried just shutting down the offending workbook and
calling it up again. No go. In fact, I've closed down all workbooks
and called up the offending workbook and it still fails. Only
restarting Excel seems to work.

Any idea how to go about testing this?

 

[Peter T]

Without seeing your code and workbooks there could be many reasons. One
thing you could try is to include an error handler in all your routines,
something like this

Public gbDebug As Boolean
Sub DebugOn()
gbDebug = True
End Sub

Sub abc()
Dim d As Double

On Error GoTo errH:

d = 1 / 0
Exit Sub ' or Exit Function
errH:
If gbDebug Then
Debug.Print Err.Number, Err.Description
Application.ScreenUpdating = True
Stop
' press F8 to resume at offending line
Resume
Else
' cater as appropriate
End If
End Sub


Run DebugOn to turn on debugging. If you have a large number of routines to
adapt MZ-Tools will help (hmm, not sure it works with Excel 97).

Unfortunately some things do not trigger an error at all, particularly in
XL97 (slight exaggeration, not often but it happens). Do you have and UDFs
in workbooks that get triggered during the process. In XL97, if code in a
UDF hits an unhandled error all code just stops, including your own, no
error message appears. Could that be a possibility.

Regards,
Peter T