Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel just dies - XL97
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 failu 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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel just dies - XL97
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 "dranon" wrote in message ... 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 failu 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA just dies - EXCEL 97 | Excel Programming | |||
OLE link between word and excel dies | New Users to Excel | |||
Application dies when you use mouse wheel in listbox | Excel Programming | |||
VBA for XL97 | Excel Programming | |||
Macro dies in the middle of execution | Excel Programming |