Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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:

  1. 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).
  2. 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.

share|improve this question
on occasion i run into similar issues, usually caused by one process not completing before the macro continues, thus leaving something out of place (line breaks actually help the issue because it gives the process a chance to complete). i would recommend commenting out any lines that disable screen updating so that you can watch the macro in action. hopefully this will give you a better idea of where it is hanging. – Xantec Mar 16 '11 at 20:33
@xantec - what do you mean "line breaks"? The screen updating is a good idea though... – Ahmad Mar 17 '11 at 4:36
@Ahmed sorry, "line breaks" = "debug break points" – Xantec Mar 17 '11 at 13:10
I have the exact same situation - xlsm created in 2007 hangs my 2010. – x0n Sep 7 '11 at 21:03
@Ahmad, in addition to the .ScreenUpdating=True and the debug by elimination, try inserting Debug.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. – Peter Albert Jan 13 '13 at 20:29

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:

Application.ScreenUpdating = False

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:

Application.ScreenUpdating = True

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.

share|improve this answer

Thanks for this help.

I was running a macro in development and live. I had no issue in DEV but it hung in live. It runs on a server rather than a local PC. I recall that when we first tried it in live we had to kill the task and it hung on subsequent retries. As soon as I added the lines application.screenupdating = TRUE doevents() doevents()

immediately before application.screenupdating = FALSE

the macro has worked as intended.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .