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

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

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

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

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