Display the current value of an Options setting
 

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

Display the current value of an Options setting
 

Your numbers are correct, but why don't you just try the built in
functionality?
=INFO("recalc")
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Michael R" wrote:

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

Display the current value of an Options setting
 

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"
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Michael R" wrote:

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

Display the current value of an Options setting
 

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" wrote:

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"
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Michael R" wrote:

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

Display the current value of an Options setting
 

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.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Michael R" wrote:

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" wrote:

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"
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Michael R" wrote:

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