Excel crashing
QuestionEdit
QUESTION: in Excel 2007, I am using spreadsheet formulae to do the mathematical calculations, with VBA for presentation and error-handling. I have had a lot of trouble with"crashing". In the middle of editing a formula, the egg-timer will suddenly appear and the VBE becomes faintly visible through the spreadsheet, gradually getting more distinct until it is quite legible. Then I get an error message saying thathas to close, but will auto-recover the file. The VBA I am running includes two OnEvent macros - one runs on the press of the ENTER key to check the input and move the cursor to the next input cell; the other is OnCalculate, in case the data is pasted, or the cursor is moved by the mouse after entry. In your experience, could these two auto-run routines be the cause of my problem? Should I disable them while I am still developing and editing the spreadsheet formulae? Thank you for your time and any insight you can offer on this matter. Terry
ANSWER: Terry,
I wouldn't think those are the cause, but the only way to know is to disable them and see if that improves the situation. It does not sound typical.
I would be curious about the enter key routine. Unless you are positioning the cursor in an unusual way, you have options to move right or down after enter inoptions. As for checking the input, there is also the data validation feature.
I know you are a long timeuser since you are using OnEnter and OnCalculate. In97, Microsoft added the Change and Calculate (and SelectionChange as well as others) events at the worksheet and workbook level. While I would expect the old methods to be even more stable, it could be that they have not been given proper attention ashas evolved. You might try using these newer events.
If nothing becomes apparent/there in no improvement, you might try Rob Bovey's code cleaner routine - it is a free utility on his site
it simply exports all code modules and saves the workbook. then reimports the code modules. This can often refresh the file and clear up odd problems.
As far as the fading you describe, I believe that is a Vista feature. I have it happend on more than just excel. Just part of Vista's error handling interface I think.
Other things you can look at are if you have any unique addins loaded. You can try unloading them or startin Safe Mode and see if that helps.
Right now I think you just have to systematically try to remove things and add them back until you find the culprit.
Also, if you control of the users actions is just for interface purposes, you might also explore using a userform(s) to get data and keep the user off the sheet (if that is a concern).
---------- FOLLOW-UP ----------
QUESTION: To satisfy your curiosity about the ENTER routine and why I don't use the built-in validation: The program is a survey application and, while user entry is in several equal-length columns, they are not regularly spaced. One of the four options for angle units is 'degrees, minutes and seconds', so each user-entered angle has to be checked for magnitude, format and its mathematical compatibility with previously-entered elements. If an error is found, conditional formatting turns the cell red and the cursor returns to that cell until the error is fixed. I don't know a lot about data validation, but I think all this might be a bit beyond it. I am running Windows XP, so I will take your advice and try the 'process of elimination' method to try and solve the crashing problem - then Rob Bovey's code cleaner if required. I guess this is not really another question, but a bit of background explanation and a thank-you all in one. Terry
AnswerEdit
Terry,
I would guess if you can use a formula to have conditional formatting turn the cell red then you could probably use the same formula (or one very similar) in data validation.
I only have2007 on a vista machine, and that is where I first saw the fading of the screen and since I saw it with other applications, I assumed it was a vista thing. Apparently not totally anyway.
Thanks for the explanation.
Advertisement