Formula Result = Volatile ??

[monir]

Hello;

1) I've noticed recently that opening the same simple w/b and Enable Macros,
ALL values on the active w/s would momentarily display correctly, and then
(generally speaking) the cells containing formulas (e.g.; =SUM(A1:A10)) would
display FALSE and those containing references would display False.
(I'm not using any volatile functions such as NOW, TIMMER, RAND, etc.)

2) Pressing F9 would remove "some" of the displayed False, despite the fact
that the Tools::Options::Calculation::Automatic is checked.

3) Selecting a cell with displayed FALSE, and pressing Enter would replace
False with the correct value.

4) If I select one of those False displayed cells, and in the Formula Bar
press fx, it would display:
....Formula result = volatile

Can someone please shed some light on what going on ??

Thank you.
(XL 2003, Win XP)

 

[Héctor Miguel]

hi, monir !

closest threads about "formula result = volatile": -> http://tinyurl.com/6lsyrf

so... what your macros do and/or "look like" ?
- module names ?
- procedure names (either sub or function) ?
- "handling" events and/or calculation process ?

what if you DO NOT enable your w/b macros ?

hth,
hector.

__ OP __

 

[monir]

Hi Hector;

1) Opening the w/b with "Disable Macros" produces #NAME? in cells with
formulas and references.

2) The procedure consists of a UDF Zroots2(,) calling Sub Laguer2(,). The
UDF is used on the w/s as an array function. The procedure works fine and as
desired.

3) There're:
- NO Events;
- NO Volatile Functions;
- NO volatile options added

4) Opening the w/b with "Enable Macros", the cells with formulas and
references display in a quick succession:
- #NAME?, then
- correct values, then
- FALSE or False, and
- Calculate appears on the Task Bar

5) Inserting the following 4 Application statements in the UDF DID NOT solve
the problem:
Function Zroots2(,)
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'.............my code 1
Call Laguer2(,)
'.............my code 2
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

6) With the open w/b and False displayed, enter:
CTRL+ALT+F9
and ALL FALSE and False are replaced with the correct values, and Calculate
disappears from the Task Bar!

7) Tried the equivalent of ctrl+alt+f 9 in a w/s activate event, but didn't
work:
Private Sub Worksheet_Activate ()
Application.CalculateFull 'ctrl+alt+f9
' Application.CalculateFullRebuild 'also didn't work
End Sub

Any suggestions ?? Thank you.
(XL 2003, Win XP)

 

[Héctor Miguel]

hi, monir !

it looks by now like a naming conflict that application (excel/vba) can not solve as clearly as expected
i.e. your procedures sub and function written in a "class" code module (worksheet/workbook code module ?)

and/or not well designed procedures (either sub or function), arguments, data type, handling ranges, etc.
at this point, i would suggest a deep visit to Charles Williams' Excel's Smart Recalculation pages:
- http://www.decisionmodels.com/calcsecrets.htm (recalculation engine)
- http://www.decisionmodels.com/calcsecretsj.htm (user defined functions)
- there are many other topics (I guess) you will need to review from that pages -?-

hth,
hector.

__ OP __

1) Opening the w/b with "Disable Macros" produces #NAME? in cells with formulas and references.

2) The procedure consists of a UDF Zroots2(,) calling Sub Laguer2(,).
The UDF is used on the w/s as an array function. The procedure works fine and as desired.

3) There're:
- NO Events;
- NO Volatile Functions;
- NO volatile options added

4) Opening the w/b with "Enable Macros", the cells with formulas and references display in a quick succession:
- #NAME?, then
- correct values, then
- FALSE or False, and
- Calculate appears on the Task Bar

5) Inserting the following 4 Application statements in the UDF DID NOT solve the problem:
Function Zroots2(,)
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'.............my code 1
Call Laguer2(,)
'.............my code 2
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

6) With the open w/b and False displayed, enter: CTRL+ALT+F9
and ALL FALSE and False are replaced with the correct values, and Calculate disappears from the Task Bar!

7) Tried the equivalent of ctrl+alt+f 9 in a w/s activate event, but didn't work:
Private Sub Worksheet_Activate ()
Application.CalculateFull 'ctrl+alt+f9
' Application.CalculateFullRebuild 'also didn't work
End Sub

__ previous post __