Unexpected file change

[Walter Briscoe]

I have a .xls file, opened by Excel 2003.
When I open it, either by clicking it, or from a shortcut, or by opening
it from Excel, or even in safe mode, it is changed and I get "Do you
want to save the changes you made to 'X.xls'?" I can open other files
without changing them.

I have a workaround.

Public Sub auto_open()
ActiveWorkbook.Saved = True
End Sub

I would like to know what sets ActiveWorkbook.Saved = False before
auto_open is run.

On a second 2003 installation, I see the same behaviour.
I also see it in 2010.

 

[joeu2004]

I have a .xls file, opened by Excel 2003.
When I open it, either by clicking it, or from a shortcut,
or by opening it from Excel, or even in safe mode, it is
changed and I get "Do you want to save the changes you made
to 'X.xls'?" I can open other files without changing them.

This can happen when opening files in Automatic calculation mode that
contain formulas that reference "volatile" functions.

Some obvious "volatile" functions that do change with each recalculation are
RAND, RANDBETWEEN, TODAY and NOW. Presumably you don't use them, because I
infer that you do not expect the file to actually change.

Some less-obvious "volatile" functions are OFFSET and INDIRECT.

But I found that this situation (prompted to save when no apparent change
was made) can also happen for uses of INDEX, which is not a "volatile"
function. However, it does not always happen.

And some uses of some objects cause this, but again not always. For
example, some charts, IIRC.

You might or might not find more information about this by reading
http://www.decisionmodels.com/calcsecretse.htm and exploring other links
there.

 

[Walter Briscoe]

This can happen when opening files in Automatic calculation mode that
contain formulas that reference "volatile" functions.

Some obvious "volatile" functions that do change with each
recalculation are RAND, RANDBETWEEN, TODAY and NOW. Presumably you
don't use them, because I infer that you do not expect the file to
actually change.

Some less-obvious "volatile" functions are OFFSET and INDIRECT.

[snip]

Thanks for a helpful reply.
I thought about volatile functions after posting.
I had considered NOW, but would not have thought of INDIRECT.
I trimmed my workbook to a single example "constant" formula
=INDIRECT("Sheet1!J"&201)

In the real file that is =INDIRECT("Sheet1!J"&$A2) where A2 is
=MATCH($B$2,Sheet1!$A:$A,0)

I can write some code to evaluate the formulae when required.
Any other ideas?

 

[Walter Briscoe]

In message <[email protected]> of Sun, 29 Jul 2012
10:49:05 in microsoft.public.excel.programming, Walter Briscoe

This can happen when opening files in Automatic calculation mode that
contain formulas that reference "volatile" functions.

Some obvious "volatile" functions that do change with each
recalculation are RAND, RANDBETWEEN, TODAY and NOW. Presumably you
don't use them, because I infer that you do not expect the file to
actually change.

Some less-obvious "volatile" functions are OFFSET and INDIRECT.

[snip]

Thanks for a helpful reply.
I thought about volatile functions after posting.
I had considered NOW, but would not have thought of INDIRECT.
I trimmed my workbook to a single example "constant" formula
=INDIRECT("Sheet1!J"&201)

In the real file that is =INDIRECT("Sheet1!J"&$A2) where A2 is
=MATCH($B$2,Sheet1!$A:$A,0)

I can write some code to evaluate the formulae when required.
Any other ideas?

Sorry to add a second reply. I have 17 sheets. In each, INDIRECT is used
13 times in a subrow.
I had used B2 in each sheet to do MATCH and avoid repetition of the
calculation.

I NOW do
D2 as =VLOOKUP($B$2,Sheet1!$A$1:$Z$512,4)
E2 as =VLOOKUP($B$2,Sheet1!$A$1:$Z$512,5)
....
P2 as =VLOOKUP($B$2,Sheet1!$A$1:$Z$512,16)

In that,
1) I don't much like Sheet1!$A$1:$Z$512.
I suppose I could use a named range for it.
Is there a way to specify the used range in a worksheet?

2) I have 4 in my formula in cells(2,4), 5 in cells(2,5), ...
16 in cells(2,16).
Is there any way to use the fact that the numbers match?

My file now opens without changing itself and seems to open much faster.
Sadly, I had not measured and did not save the file before change.

 

[Walter Briscoe]

In message <[email protected]> of Mon, 30 Jul 2012
09:12:13 in microsoft.public.excel.programming, Walter Briscoe

In message <[email protected]> of Sun, 29 Jul 2012
10:49:05 in microsoft.public.excel.programming, Walter Briscoe

I have a .xls file, opened by Excel 2003.
When I open it, either by clicking it, or from a shortcut,
or by opening it from Excel, or even in safe mode, it is
changed and I get "Do you want to save the changes you made
to 'X.xls'?" I can open other files without changing them.

This can happen when opening files in Automatic calculation mode that
contain formulas that reference "volatile" functions.

Some obvious "volatile" functions that do change with each
recalculation are RAND, RANDBETWEEN, TODAY and NOW. Presumably you
don't use them, because I infer that you do not expect the file to
actually change.

Some less-obvious "volatile" functions are OFFSET and INDIRECT.

[snip]

Thanks for a helpful reply.
I thought about volatile functions after posting.
I had considered NOW, but would not have thought of INDIRECT.
I trimmed my workbook to a single example "constant" formula
=INDIRECT("Sheet1!J"&201)

In the real file that is =INDIRECT("Sheet1!J"&$A2) where A2 is
=MATCH($B$2,Sheet1!$A:$A,0)

I can write some code to evaluate the formulae when required.
Any other ideas?

Sorry to add a second reply. I have 17 sheets. In each, INDIRECT is used
13 times in a subrow.
I had used B2 in each sheet to do MATCH and avoid repetition of the
calculation.

I NOW do
D2 as =VLOOKUP($B$2,Sheet1!$A$1:$Z$512,4)
E2 as =VLOOKUP($B$2,Sheet1!$A$1:$Z$512,5)
...
P2 as =VLOOKUP($B$2,Sheet1!$A$1:$Z$512,16)

In that,
1) I don't much like Sheet1!$A$1:$Z$512.
I suppose I could use a named range for it.
Is there a way to specify the used range in a worksheet?

2) I have 4 in my formula in cells(2,4), 5 in cells(2,5), ...
16 in cells(2,16).
Is there any way to use the fact that the numbers match?

Indeed, there is! COLUMN() and ROW() have the useful feature of
accessing the current cell, when given no argument. I have a named range
called Stations and all the relevant cells are set to
"=VLOOKUP($B$2,Stations,COLUMN())".