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.
.ScreenUpdating=True
and the debug by elimination, try insertingDebug.Print Now(), 'some marker text'
every few lines. Then take a look at the Immediate window while the code is running and see if and where it stalls. Then narrow it down in the area it stalls by adding more print statements/eliminating some code.