I have a spreadsheet with several macros. Generally, when previously using Excel 2007, a user clicks a button and everything works as expected (calculations, some email sending & file I/O). Typically, the expected run-time is about 90 seconds. The spreadsheet is a xlsm file created with Excel 2007.
With Excel 2010 however, the same user process results in a non-responsive excel and forces us to kill excel from the task manager.
Some note that I have gathered so far in trying to debug this issue:
- When monitoring CPU usage, it seems that Excel does start the macro. CPU usage increases as expected to about 47% for a few seconds. Excel.exe than drops to 0% usage and I now have a non-responsive Excel (even after 1 hour).
- If I set debug break points across modules and different functions and step through the code (after clicking the button) , the process works as expected albeit much slower. To add, there were no exceptions.
I am at a complete loss as to what the issue may be. I initially thought it may be the add in that is being used but that was debunked by point 2.
This seems to be a very odd situation. I can provide more information if required, but I'm at wits end about the root cause could be. I need help in diagnosing and resolving this issue.
Best Answer
This may not be your answer, but it is a situation that I have seen that produces the results you've described.
Look in the macro(s) for the following statement:
This tells the Excel GUI interface to not show any changes/updates/anything that is going on. This can be desirable while a macro runs. However, you have to make sure the macro at some point sets this back to true like this:
If this is never set back to True, then it produces a situation like you've described.
First search the code for the 'ScreenUpdating' property being set, remove it, and see if that fixes the issue.
If you don't find 'ScreenUpdating' anywhere in the code, then you have some other problem.
Edit: Looks like someone already mentioned screen updating in the comments above.