The quirks of "Save As..."

[David J Richardson]

(previously posted in microsoft.public.excel.programming, to no
response)

I have a rather large (in terms of code, number of controls, and data
stored in cells) spreadsheet in Excel 2007 which intermittently fails
when running VBA code (and may or may not result in Excel crashing
shortly thereafter). Doing a "Save As..." (or two, or three, or more,
if I'm unlucky) will produce a "good copy" which runs happily.

Further, what constitutes a "good copy" varies from machine to machine
(the spreadsheet is used on up to ten machines at times), with no
pattern arising over time to show particular machines are "luckier".

The places the code break are limited and predictable, and are not
unusual lines in and of themselves. As noted the solution to the code
not working is nothing to do with editing the code (or indeed any other
part of the spreadsheet), just doing a "Save As...".

Any suggestions of what "Save As..." does that may explain this, and
perhaps therefore other methods to resolve this more permanently/more
easily?

P.S. In investigating this, I noted "Save As..." is not deterministic
-- ie. doing two "Save As..."s from the same file may create two
different files (I have no idea if there is a functional difference;
I'm just noting a size difference). Dunno if this plays any part in my
varying luck when trying to create a "good copy" on the different
machines, starting from the same base file in each case.

 

[Jim Rech]

Let us hope that the SP1 patch, rumored to become available on December 11,
is the answer to this and all the other Excel 2007 quirks and problem
peoples have reported.

--
Jim
| (previously posted in microsoft.public.excel.programming, to no
| response)
|
| I have a rather large (in terms of code, number of controls, and data
| stored in cells) spreadsheet in Excel 2007 which intermittently fails
| when running VBA code (and may or may not result in Excel crashing
| shortly thereafter). Doing a "Save As..." (or two, or three, or more,
| if I'm unlucky) will produce a "good copy" which runs happily.
|
| Further, what constitutes a "good copy" varies from machine to machine
| (the spreadsheet is used on up to ten machines at times), with no
| pattern arising over time to show particular machines are "luckier".
|
| The places the code break are limited and predictable, and are not
| unusual lines in and of themselves. As noted the solution to the code
| not working is nothing to do with editing the code (or indeed any other
| part of the spreadsheet), just doing a "Save As...".
|
| Any suggestions of what "Save As..." does that may explain this, and
| perhaps therefore other methods to resolve this more permanently/more
| easily?
|
| P.S. In investigating this, I noted "Save As..." is not deterministic
| -- ie. doing two "Save As..."s from the same file may create two
| different files (I have no idea if there is a functional difference;
| I'm just noting a size difference). Dunno if this plays any part in my
| varying luck when trying to create a "good copy" on the different
| machines, starting from the same base file in each case.
|
| --
| David J Richardson -- (e-mail address removed)
| http://davidj.richardson.name/ - Dr Who articles/interviews/reviews
| http://www.boomerang.org.au/ - Boomerang Association of Australia

 

[David J Richardson]

| I have a rather large (in terms of code, number of controls, and data
| stored in cells) spreadsheet in Excel 2007 which intermittently fails
| when running VBA code (and may or may not result in Excel crashing
| shortly thereafter). Doing a "Save As..." (or two, or three, or more,
| if I'm unlucky) will produce a "good copy" which runs happily.

Let us hope that the SP1 patch, rumored to become available on December 11,
is the answer to this and all the other Excel 2007 quirks and problem
peoples have reported.

Touch a very large piece of wood, but from the official list of changes
today...

"When you use VBA code in Excel 2007, runtime errors and access
violations occur and then Excel 2007 crashes. This problem does not
occur in Excel 2003."

This matches my experience, and some testing today indicates (oh please)
my use of Excel is about to become a *lot* less traumatic...