Volatility, recalculation

[Wild Bill]

On a single worksheet I have a few cells in column B like
=FnHoursRemaining(A1)

Here's the UDF:
function FnHoursRemaining(strDate As String)
'sample input: Nov-25-07 20:16:49
FnHoursRemaining = 24 * (CDate(strDate) - Now)
end function

The function works. My question is how and why individual col. B cells
do or don't recalc on XL97 (feel free to report version differences;
this is all I'm running on this machine right now). Yes, calc is
automatic.
A. F9, alt-F9, and shift-F9 do nothing. I went Ctrl-A twice before doing
them. So EVERYTHING was selected.
B. Opening the file ("yes, enable macros") does NOT recalculate them.
C. F2 and enter key on individual cells seems to be the only way to
update the cells!!

1. I "get" that there is no volatility from Now as it's buried in the
function. But doesn't simply using a UDF make a worksheet volatile? If
so, shouldn't it recalculate?
2. What impact would application.volatile have here?
3. What in the wild world is up with result "A" above?! I keep pinching
myself to see a stupid user error/oversight. But it's really doing this.

I am reasonably familiar with range (cell) properties. Is the workbook
simply opening and sticking with .Value?

 

[Wild Bill]

I suppose from Dick's
http://www.dailydoseofexcel.com/archives/2005/04/29/using-volatile-functions/
and Charles'
http://www.decisionmodels.com/calcsecretsi.htm

that the use of application.volatile is indicated. But what about F9 et
al doing nada? I even tried ctrl-alt-shift-F9 ! (Nothing budged)

 

[Jim Rech]

I tried your function in Excel 97. With Application.Volatile the value
return in the spreadsheet recalced with every F9. Without it, it didn't.
However with Ctrl-Shift-F9 (not "ctrl-alt-shift-F9"), it recalced. So it
seems to be okay as far as I can tell.

 

[Wild Bill]

Yes. With application.volatile, F9 and Shift-F9 update. But
Ctrl-Shift-F9 never updates, ever.

Two things remain disturbing to my sanity, forcing me to now question if
instead of a semi-advanced user I am instead clueless: 1. that in the
absence of the keyword, Calculate All does not calculate all. That makes
me quiver. 2. Opening a workbook does not calculate all. Well, okay,
maybe it uses .value or .value2. 3. Option "recalculate before save" is
unchecked - but so what? My calculation is automatic (and no other
workbooks are interfering thereof, BTW).

I guess it comes down to item 1. not exercising UDFs? If truly so,brrr!

 

[Wild Bill]

Apparently there was corruption. Rebuilding cured anomalies.

What will it take for MS to escalate the issue of corruption? It's
devastatingly serious, relegating any spreadsheet into unreliability.
ANY spreadsheet. ANY. Warren Buffett's and Bill G.'s own personal
workbooks as well. (Not to mention this waste of Jim Rech's valuable
time, and other important readers' - and the waste of my own.)

If this occurred in a Borland product a whole crew would pull all
nighters until either eliminated or at *minimum* a perfect detection
method was devised ... not out at some rave party, or off-roading and
doing bongs this weekend, MUCH LESS THE PAST 500 WEEKENDS.

Oops - what am I thinking! They killed Borland! So no worries - bong on!

 

[Tim Zych]

Me neither. Ctrl + Shift + F9 doesn't recalc the UDF, but Ctrl + Alt + F9
does, consistently.

Is Ctrl + Shift + F9 is supposed to recalc? Doesn't when I enter a formula,
say =NOW() in a cell, and press Ctrl + Shift + F9. Nothing recalcs. Maybe
they changed the keyboard shortcuts for later versions of XL(?) Don't have
XL97.


XL2003.

 

[Wild Bill]

Thank you for your comments. Per Charles' fine site
http://www.decisionmodels.com/calcsecretsg.htm

"Full Calculation and Re-Calculation.
Normally Excel calculates each cell as you enter it, and minimises
calculation by only recalculating all the dependents of all the changed
cells in all the open Workbooks.
You can request a full calculation (all formulae) by pressing
Ctrl-Alt-F9, or a recalculation (all new/changed/volatile formulae,
cells and their dependents) by pressing F9.
Usually (but not always) a recalculation is faster than a full
calculation.
In Excel 2002 you can request a full calculation with dependency tree
rebuild by pressing Ctrl-Alt-Shift-F9."

Read further at the link regarding Shift-F9 (selected worksheet recalc).
I have not seen any reference anywhere to Ctrl-Shift-F9. It appears
that the only version-affected operation is Ctrl-Alt-Shift-F9.

As to my original questions in this thread, (evidently) corruption
caused me to do false reporting. I'm only a few hours of labor lighter,
and correspondingly heavier with fury, that MS does not appropriately
address corruption. (BTW, I had built the sheet 100% from scratch, with
no abends/power outs. The 100% rebuild fixed things. )

At this point, having a column full of cells using UDFs, they each
recalculate
- when workbook is opened
- when any cell anywhere on the sheet is changed
- and note, there is NOT application.volatile in the UDF (or any VBA).

This is how I would have expected things to operate to begin with. I'm
sorry for earlier barking down wrong paths based on GIGO.

 

[Tim Zych]

Not sure I follow. Is Ctrl + Shift + F9 supposed to do anything? It seems to
prevent calculation on XL2003, even for the simplest of non-UDF XL formulas.

Going back to the first posting:

A. F9, alt-F9, and shift-F9 do nothing. I went Ctrl-A twice before doing
them. So EVERYTHING was selected.

Same here.

B. Opening the file ("yes, enable macros") does NOT recalculate them.

Same here.

C. F2 and enter key on individual cells seems to be the only way to
update the cells!!

Same here. Except for a full recalculation using Ctrl + Alt + F9, which was
not mentioned.

When I perform the same steps as you, I get exactly the same results in new
workbooks on XL2003.

At this point, having a column full of cells using UDFs, they each
recalculate
- when workbook is opened
- when any cell anywhere on the sheet is changed
- and note, there is NOT application.volatile in the UDF (or any VBA).

The same UDF as you first posted, non-volatile, referring to one cell, with
a constant datetime in A1?

The help file says:

"A nonvolatile function is recalculated only when the input variables
change."

If I change A1, referred to by the FnHoursRemaining(), it recalculates.

How should it work? Sorry if I am ignorant of your experiences..but I don't
see the problem. It seems to be functioning as designed. Ctrl + Alt + F9
calculates it, as does Application.CalculateFull on my machine.