D
David Benson
I apologize ahead of time for the length of this post.
I have run into a problem with Excel that is driving me nuts!
I am using Excel 2003 (the version that is in Office XP) running on Win2000.
I have a fairly large (~40M) set of spreadsheets that I use to maintain
status data on development projects, and to produce status charts. I have
written a set of macros to assist users in maintaining the status data and
to calculate and produce the required charts. This is implemented in two
workbooks: a large workbook in which I keep the data, and a second, much
smaller workbook that contains all of the charts. I do it this way because
I actually have three different versions of the database for projects that
are a part of three different developments. The charts workbook can be used
by all three.
My problem is this: every once in a while, with no error message or
warning, Excel stops updating values when I do a recalculation. I can
proceed merrily along with my work, or my macros can chug merrily along
extracting data and producing status charts, but they all look the same
because no values are being updated. If I edit a cell, the value gets
updated when I press <ENTER>, but not as a result of a recalculation. It
doesn't matter whether the recalculation is partial or full.
I've done some research on-line and have found very little about this
problem. I did find a couple of articles in the MS Knowledge Base that
might be pertinent. They say that Excel 2003 builds an internal table that
it uses to determine the proper sequence of calculations when a
recalculation is performed. It does this so it doesn't recalculate one cell
whose value depends on another cell that has not yet been recalculated, and
also so that it does not recalculate cells that don't need it. (This
approach is new to Excel 2003.) Apparently, the internal table that Excel
constructs can only contain 65,536 entries. If number of table entries
exceeds that, then Excel is supposed to just recalculate every cell. A
symptom of the situation where the table size has been exceeded is that the
"Calculate" notice in the Status bar does not go away, even if you do a full
recalculation.
I discovered that one can force Excel to rebuild this table by pressing
CTRL-ALT-SHIFT-F9. If you try this (at least on a large spreadsheet), you
will find that Excel appears to just sit there for a significant period of
time, then does a complete recalculation. The apparent idle period occurs
while the table is being rebuilt.
I have implemented a workaround by placing a step in my macros to do this
rebuild operation (the VBA method is "application.calculatefullrebuild") at
places where the original problems seems to show up -- or at least, where
the its effect is most obnoxious. This appears to be working. However, I
sure would like to know what's going on. I would also like to make MS aware
of this situation, so they can determine if there really is an underlying
problem.
Anyone of you Excel MVPs have any ideas? How can I report this to MS?
Thanks,
David Benson
I have run into a problem with Excel that is driving me nuts!
I am using Excel 2003 (the version that is in Office XP) running on Win2000.
I have a fairly large (~40M) set of spreadsheets that I use to maintain
status data on development projects, and to produce status charts. I have
written a set of macros to assist users in maintaining the status data and
to calculate and produce the required charts. This is implemented in two
workbooks: a large workbook in which I keep the data, and a second, much
smaller workbook that contains all of the charts. I do it this way because
I actually have three different versions of the database for projects that
are a part of three different developments. The charts workbook can be used
by all three.
My problem is this: every once in a while, with no error message or
warning, Excel stops updating values when I do a recalculation. I can
proceed merrily along with my work, or my macros can chug merrily along
extracting data and producing status charts, but they all look the same
because no values are being updated. If I edit a cell, the value gets
updated when I press <ENTER>, but not as a result of a recalculation. It
doesn't matter whether the recalculation is partial or full.
I've done some research on-line and have found very little about this
problem. I did find a couple of articles in the MS Knowledge Base that
might be pertinent. They say that Excel 2003 builds an internal table that
it uses to determine the proper sequence of calculations when a
recalculation is performed. It does this so it doesn't recalculate one cell
whose value depends on another cell that has not yet been recalculated, and
also so that it does not recalculate cells that don't need it. (This
approach is new to Excel 2003.) Apparently, the internal table that Excel
constructs can only contain 65,536 entries. If number of table entries
exceeds that, then Excel is supposed to just recalculate every cell. A
symptom of the situation where the table size has been exceeded is that the
"Calculate" notice in the Status bar does not go away, even if you do a full
recalculation.
I discovered that one can force Excel to rebuild this table by pressing
CTRL-ALT-SHIFT-F9. If you try this (at least on a large spreadsheet), you
will find that Excel appears to just sit there for a significant period of
time, then does a complete recalculation. The apparent idle period occurs
while the table is being rebuilt.
I have implemented a workaround by placing a step in my macros to do this
rebuild operation (the VBA method is "application.calculatefullrebuild") at
places where the original problems seems to show up -- or at least, where
the its effect is most obnoxious. This appears to be working. However, I
sure would like to know what's going on. I would also like to make MS aware
of this situation, so they can determine if there really is an underlying
problem.
Anyone of you Excel MVPs have any ideas? How can I report this to MS?
Thanks,
David Benson