Recalculation does not work

[Joe M.]

I have a worksheet with a UDF that displays the last saved date. There are
other cells which have dependancies on this date. When saving and reopening
the document the date remains unchanged. I have tried F9, Shift + F9, CTRL +
ALT + F9, CTRL + SHIFT + ALT + F9 and also changing the settings in OPTIONS >
CALCULATION from automatic to manual back to automatic. Recalculate before
save is checked. When I close the workbook and reopen the date remains the
same. If I retype the formula in another cell then the date displays
correctly. Can anyone help?

Much appreciated!
Joe M.

 

[Jim May]

Joe Post the UDF code

 

[Joe M.]

Jim,
Here's the UDF:

Function lastsaved() As Double
lastsaved = ActiveWorkbook.BuiltinDocumentProperties(12)
End Function

Thanks again
Joe M.

 

[Charles Williams]

Sounds like you need to make the UDF volatile.

Charles
_________________________________________
FastExcel 2.3
Name Manager 4.0
http://www.DecisionModels.com

 

[Joe M.]

I added to the UDF so now it appears as follows but F9 and the same attempts
to recalculate still fail.

Function lastsaved() As Double
Application.Volatile
lastsaved = ActiveWorkbook.BuiltinDocumentProperties(12)
End Function

I must have missed something.

Thanks,
Joe M.

 

[Jim May]

Enter in a blank cell (somewhere) the function =now()
This is a volatile function that will cause calculation to take place.

 

[Joe M.]

Jim,

I did that but the UDF still did not recalculate.


Thanks,
Joe

 

[joeu2004]

I added to the UDF so now it appears as follows but F9 and
the same attempts to recalculate still fail.

Is anything else not recalculating; or just this UDF?

Wild-ass guess: try executing the following macro, substituting the
proper worksheet name or looping for all worksheets:

Sub doit()
Worksheets("Sheet1").EnableCalculation = True
End Sub

If that works, be sure to save the workbook, after deleting the "doit"
macro, if you wish.

Setting EnableCalculation to False is really weird, IMHO. If I save
and reopen a workbook after setting EnableCalculation to False, any
RAND() references cease to calculate, but any newly added RAND()
references retain their volatile nature. It is as if setting
EnableCalculation to False affects the state of only used cells at the
time it is set. And it appears that the cell state is a "property" of
the cell, although I don't know what Property of the cell object might
be affected by it.

Note: To test the effectiveness of setting EnableCalculation to True,
then saving and reopening the workbook, it would be prudent to set
macro security to Very High or at least to Medium (and do not enable
macros). I think Very High would be better, on the off-chance (albeit
unlikely) that a "safe" macro is the cause of setting
EnableCalculation to False.

Again, this is a wild-ass guess with near-zero chance of being
effective, I think. But if nothing else explains your problem, this
might be worth a try.

 

[Charles Williams]

Works fine for me.
If in Automatic mode it shows the last save time when saved, closed and then
reopened.
If in Manual mode you have to press F9

regards
Charles
_________________________________________
FastExcel 2.3
Name Manager 4.0
http://www.DecisionModels.com