Display the current value of an Options setting

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Michael R - 11 Dec 2007 14:48 GMT

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

Reply to this Message

John Bundy - 11 Dec 2007 15:19 GMT

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

Signature

-John
Please rate when your question is answered to help us and others know what
is helpful.

> I want to display the current setting of Tools / Options / Calculation
> ("Manual" or "Automatic" or "Automatic except tables") in a cell of my
[quoted text clipped - 17 lines]
> -4105 = Automatic
> 2 = Semi Automatic

Reply to this Message

John Bundy - 11 Dec 2007 15:20 GMT

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"

Signature

-John
Please rate when your question is answered to help us and others know what
is helpful.

Reply to this Message

Michael R - 11 Dec 2007 15:28 GMT

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)

> Or if you want it in code do it the easy way;
> If Application.Calculation = xlCalculationManual Then calcmode "Manual"
[quoted text clipped - 23 lines]
> > -4105 = Automatic
> > 2 = Semi Automatic

Reply to this Message

John Bundy - 11 Dec 2007 15:52 GMT

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.

Signature

-John
Please rate when your question is answered to help us and others know what
is helpful.

> John,
>
[quoted text clipped - 34 lines]
> > > -4105 = Automatic
> > > 2 = Semi Automatic

Reply to this Message