Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Disabling Tools/Options setting | Excel Discussion (Misc queries) | |||
Setting Print options from a VB6 program | Excel Programming | |||
Setting print options globally ? | Excel Discussion (Misc queries) | |||
Setting Printer Options Programmatically | Excel Programming | |||
Setting the Options of the Find method | Excel Programming |