Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 have you tried commenting out function calls and debug by elimination? –  Jake Mar 6 '12 at 13:21

1 Answer 1

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.