D
DonJ_Austin
I have spent hours just trying to isolate the conditions and effects
surrounding some corruption that has taken place in the list of Defined Names
in an MS Excel 2007 workbook. I am convinced it is a bug in Excel that is
closely associated with bugs I have previoulsy reported and were (at least
partially) resolved. Searching the Discussion forums for [Excel 2007 copy
worksheet "defined name"], I found several reports that seem to touch on
similar phenomena, especially the first article cited, and it's similar to
other bugs I reported 2 years ago related to links (which Microsoft still has
trouble with).
Here are the isolations. I had 2 workbooks (programs) with a good bit of
Defined Names, VBA, etc definitely more than the average amateur spreadsheet
for someone's bowling scores. I copied a worksheet from workbook1 to
workbook2. During the process I got the poorly worded message that the
worksheet I am copying "contains" a cited Defined Name that exists in the
"destination worksheet" and that dialog box cycled through several Defined
Names that were NOT associated with the worksheet in question. When I
answered "Yes" (supposedly to use the Defined Names as defined in the
"destination sheet") NEW names were created in the destination workbook,
which were duplicative of existing Defined Names in the destination workbook.
Those new Defined Names had a value of #REF and could not be deleted.
When I started again with the two workbooks, saved in a state BEFORE the
copy operation described above, and chose "No" in the dialog box (supposedly
to "rename the range referred to in the formula or worksheet", I then chose a
different and distinctive name for each of the Defined Names being treated
(goober1, goober2, etc). The results on that experiment were: (1) the
corrupted and duplicative Defined Names were still created in the destination
workbook, (2) in the destination workbook there were no Defined Names with
the names I provided (goober1, goober2, etc), (3) the corrupted Defined Names
were no longer shown in the source workbook, and (4) in the source workbook
there were no Defined Names with the names I provided (goober1, goober2, etc).
No conclusion is that the two methods for dealing with Defined Names related
to copying worksheets are BOTH buggy (Excel internal code), based solely on
the two carefully isolated and observed tests described above, relative to
what the dialog boxes offer and describes as the functions, and the results.
Since it is impossible to predict the scope of buggy code I cannot see, I
believe that buggy code may well have oringally created the corrupted Defined
Names in the source workbook described above.
Please don't tell me I can work around this problem by recreating the
worksheet entirely in the destination workbook.
My greater concerns are that: (1) this is a bug that Microsoft will deny for
more time than I would like to waste, (2) it is the insidious kind of bug
that you don't necessarily realize immedaitely until later something else
goes wrong, (3) it creates a condition in which Microsoft just says "the file
is corrupted" like that excuses the bug that created the corruption, and (4)
it shows again how poor the coding and testing is with Excel12. I have the
two workbooks in which I have isolated the above replicatable behaviors and
will share them with Microsoft support/devel or any MVPs.
Can anyone help get this recognized and fixed by Microsoft ?
Can anyone convince me that Microsoft will ever get Excel12 working at least
as well as Excel11 did and that I should not change careers so I don't have
to waste SO MUCH time trying to develop or support Excel12-based programs ?
I'm serious.
surrounding some corruption that has taken place in the list of Defined Names
in an MS Excel 2007 workbook. I am convinced it is a bug in Excel that is
closely associated with bugs I have previoulsy reported and were (at least
partially) resolved. Searching the Discussion forums for [Excel 2007 copy
worksheet "defined name"], I found several reports that seem to touch on
similar phenomena, especially the first article cited, and it's similar to
other bugs I reported 2 years ago related to links (which Microsoft still has
trouble with).
Here are the isolations. I had 2 workbooks (programs) with a good bit of
Defined Names, VBA, etc definitely more than the average amateur spreadsheet
for someone's bowling scores. I copied a worksheet from workbook1 to
workbook2. During the process I got the poorly worded message that the
worksheet I am copying "contains" a cited Defined Name that exists in the
"destination worksheet" and that dialog box cycled through several Defined
Names that were NOT associated with the worksheet in question. When I
answered "Yes" (supposedly to use the Defined Names as defined in the
"destination sheet") NEW names were created in the destination workbook,
which were duplicative of existing Defined Names in the destination workbook.
Those new Defined Names had a value of #REF and could not be deleted.
When I started again with the two workbooks, saved in a state BEFORE the
copy operation described above, and chose "No" in the dialog box (supposedly
to "rename the range referred to in the formula or worksheet", I then chose a
different and distinctive name for each of the Defined Names being treated
(goober1, goober2, etc). The results on that experiment were: (1) the
corrupted and duplicative Defined Names were still created in the destination
workbook, (2) in the destination workbook there were no Defined Names with
the names I provided (goober1, goober2, etc), (3) the corrupted Defined Names
were no longer shown in the source workbook, and (4) in the source workbook
there were no Defined Names with the names I provided (goober1, goober2, etc).
No conclusion is that the two methods for dealing with Defined Names related
to copying worksheets are BOTH buggy (Excel internal code), based solely on
the two carefully isolated and observed tests described above, relative to
what the dialog boxes offer and describes as the functions, and the results.
Since it is impossible to predict the scope of buggy code I cannot see, I
believe that buggy code may well have oringally created the corrupted Defined
Names in the source workbook described above.
Please don't tell me I can work around this problem by recreating the
worksheet entirely in the destination workbook.
My greater concerns are that: (1) this is a bug that Microsoft will deny for
more time than I would like to waste, (2) it is the insidious kind of bug
that you don't necessarily realize immedaitely until later something else
goes wrong, (3) it creates a condition in which Microsoft just says "the file
is corrupted" like that excuses the bug that created the corruption, and (4)
it shows again how poor the coding and testing is with Excel12. I have the
two workbooks in which I have isolated the above replicatable behaviors and
will share them with Microsoft support/devel or any MVPs.
Can anyone help get this recognized and fixed by Microsoft ?
Can anyone convince me that Microsoft will ever get Excel12 working at least
as well as Excel11 did and that I should not change careers so I don't have
to waste SO MUCH time trying to develop or support Excel12-based programs ?
I'm serious.