Formula Result = Volatile ??

[monirg]

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.

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 its 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.

 

[GlennUK]

It doesn't sound like a simple workbook ... you have macros in there ... maybe with Functions that are referenced in formulae?

 

[monirg]

Hi Glenn;

Here're some additional info.

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
End Function

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 kindly.