Formula Result = Volatile ??

monirg

Well-known Member
Joined
Jan 11, 2005
Messages
629
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.
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,539
It doesn't sound like a simple workbook ... you have macros in there ... maybe with Functions that are referenced in formulae?
 

monirg

Well-known Member
Joined
Jan 11, 2005
Messages
629
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:
Code:
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:
Code:
Private Sub Worksheet_Activate ()
  Application.CalculateFull  'ctrl+alt+f9
' Application.CalculateFullRebuild  'also didn't work
End Sub
Any suggestions ?? Thank you kindly.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,307
Messages
5,600,869
Members
414,411
Latest member
Snowmanaus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top