Display the current value of an Options setting

[Michael R]

I want to display the current setting of Tools / Options / Calculation
("Manual" or "Automatic" or "Automatic except tables") in a cell of my
workbook.

Question 1:
The following UDF does half-work: it displays correctly when I switch from
Manual to Automatic but not the other way round. What's wrong?

Function Get_Calc(Sheet As String) As Long
With Application
Get_Calc = .Calculation
End With
End Function

Question 2:
Only by trying have I found the following results from my UDF - is that
correct? where can I find a comprehensive overview of these value/setting
correlations?
-4135 = Manual
-4105 = Automatic
2 = Semi Automatic

 

[John Bundy]

Your numbers are correct, but why don't you just try the built in
functionality?
=INFO("recalc")

 

[John Bundy]

Or if you want it in code do it the easy way;
If Application.Calculation = xlCalculationManual Then calcmode "Manual"
If Application.Calculation = xlCalculationAutomatic Then calcmode "Automatic"
If Application.Calculation = xlCalculationSemiautomatic Then calcmode
"Semi-Automatic"

 

[Michael R]

John,

Thanks for that - I was not aware of the Info("recalc") function.

But:

It has the same "problem" as my UDF: when switching from automatic to
manual, the function would not update and remain on automatic.
Can we do anything about that?
(I work with 2002/SP3)

 

[John Bundy]

There is really nothing that will do it all completely automatically.
Application.Volatile will force a UDF to update but calculation has to be on.
You can update it on a selection change of any kind but only by recalculating
the whole sheet(which defeats the purpose) or by knowing where each function
is and recalc just that cell.