Large Spreadsheet - Freeze On Recalculate

[Guest]

I am having a persistent problem. Every time I tell the spreadsheet to
recalculate (F9) Excel 2003 freezes. After I press F9 it says "Calculating
Cells: 0%". It stays at 0% and will not progress. Eventually the Task Manager
sayd says that Excel is Not Responding. For what its worth, the program/file
name is listed twice in the Task Manager. Any suggestions? I can't figure out
what to do.

 

[Guest]

Try a couple of tests. Open up the workbook and check the Task Manager for
entries. Then hit [F9] and check again. If the second copy shows up then
I'd suspect some code somewhere attached to a sheet's Calculate event
triggering and firing off and maybe confusing everything. Call this a stab
in the dark, but it is something to check out if for no other reason than to
rule code out of the picture.

 

[Guest]

You are correct. When I hit F9, or even Shift+F9, the second reference to
Excel (with the same file name listed each time) is listed on the Task
Manager. Any ideas on how to resolve this?

Try a couple of tests. Open up the workbook and check the Task Manager for
entries. Then hit [F9] and check again. If the second copy shows up then
I'd suspect some code somewhere attached to a sheet's Calculate event
triggering and firing off and maybe confusing everything. Call this a stab
in the dark, but it is something to check out if for no other reason than to
rule code out of the picture.

I am having a persistent problem. Every time I tell the spreadsheet to
recalculate (F9) Excel 2003 freezes. After I press F9 it says "Calculating
Cells: 0%". It stays at 0% and will not progress. Eventually the Task Manager
sayd says that Excel is Not Responding. For what its worth, the program/file
name is listed twice in the Task Manager. Any suggestions? I can't figure out
what to do.

 

[Guest]

Quite frankly, I don't have an immediate answer. I had a situation recently
where this kind of thing happened - an Excel file that had been working
faithfully and flawlessly for months suddenly locked up and I noticed that
there was a second instance of Excel visible in the Windows status bar
(didn't look in Task Manager).

In my case it turned out to be a special case that had not been considered
in the code design. It was creating what turned out to be a
circular-reference formula in a cell and the when it did that, everything
froze up, I had more instances of Excel running than I could account for. My
fix was to track down the coding problem and fix it. I suspect that the
second instance of Excel that I was seeing was probably the error
notification about a circular reference, but since the system had essentially
gone into an infinite loop, I never actually got to see that notice on screen.

One thing you might try is when it gets into that situation, see if you can
open up Task Manager and try to "Switch to..." the various instances of Excel
listed. That might allow you to at least view them both and see what they
really are: maybe one will turn out to be the workbook, the other some kind
of system alert.

JLatham

You are correct. When I hit F9, or even Shift+F9, the second reference to
Excel (with the same file name listed each time) is listed on the Task
Manager. Any ideas on how to resolve this?

Try a couple of tests. Open up the workbook and check the Task Manager for
entries. Then hit [F9] and check again. If the second copy shows up then
I'd suspect some code somewhere attached to a sheet's Calculate event
triggering and firing off and maybe confusing everything. Call this a stab
in the dark, but it is something to check out if for no other reason than to
rule code out of the picture.

I am having a persistent problem. Every time I tell the spreadsheet to
recalculate (F9) Excel 2003 freezes. After I press F9 it says "Calculating
Cells: 0%". It stays at 0% and will not progress. Eventually the Task Manager
sayd says that Excel is Not Responding. For what its worth, the program/file
name is listed twice in the Task Manager. Any suggestions? I can't figure out
what to do.

 

[Guest]

Thanks for the suggestion. The spreadsheet is working now.

I'm not sure just what fixed it but I did find some external data references
that were broken (I had moved the source data). I fixed these.

The other thing I did was to do a Search and Replace. I had Excel replace
every = with a =. I think that I did this two times. Somewhere else I read
that this will rebuild some calculation table. May have been my imagination
but it did seam to help. Each time it was repeated the search and replace I
think the calculation would hang at a higher percent complete (i.e. 28%
instead of 0%).

Thanks for your input. I will try your suggestion the next time this happens.

J

Quite frankly, I don't have an immediate answer. I had a situation recently
where this kind of thing happened - an Excel file that had been working
faithfully and flawlessly for months suddenly locked up and I noticed that
there was a second instance of Excel visible in the Windows status bar
(didn't look in Task Manager).

In my case it turned out to be a special case that had not been considered
in the code design. It was creating what turned out to be a
circular-reference formula in a cell and the when it did that, everything
froze up, I had more instances of Excel running than I could account for. My
fix was to track down the coding problem and fix it. I suspect that the
second instance of Excel that I was seeing was probably the error
notification about a circular reference, but since the system had essentially
gone into an infinite loop, I never actually got to see that notice on screen.

One thing you might try is when it gets into that situation, see if you can
open up Task Manager and try to "Switch to..." the various instances of Excel
listed. That might allow you to at least view them both and see what they
really are: maybe one will turn out to be the workbook, the other some kind
of system alert.

JLatham

You are correct. When I hit F9, or even Shift+F9, the second reference to
Excel (with the same file name listed each time) is listed on the Task
Manager. Any ideas on how to resolve this?

Try a couple of tests. Open up the workbook and check the Task Manager for
entries. Then hit [F9] and check again. If the second copy shows up then
I'd suspect some code somewhere attached to a sheet's Calculate event
triggering and firing off and maybe confusing everything. Call this a stab
in the dark, but it is something to check out if for no other reason than to
rule code out of the picture.

:

I am having a persistent problem. Every time I tell the spreadsheet to
recalculate (F9) Excel 2003 freezes. After I press F9 it says "Calculating
Cells: 0%". It stays at 0% and will not progress. Eventually the Task Manager
sayd says that Excel is Not Responding. For what its worth, the program/file
name is listed twice in the Task Manager. Any suggestions? I can't figure out
what to do.