Can i show in a cell whether "Recalculate" is on or off

[simon clark]

My spreadsheet has over 30,000 formula (due to a large 37 x 37 grid) and it takes about 2 minutes to recalculate. This is annoying when the system freezes whilst recalculating so it is easier to turn it off until i have finished entering data.I know it is possible to turn "recalculate" on and off by macro, but:-

1.Is it possible to display the recalc status in a cell so i know whether or not it is turned on or off.

Additionally (not really to do with this problem - just a general question):-
2. I have only ever used VBA by macro i.e. click a button to run the macro. Is it possible to make the VB code run in the background constantly without having to trigger the event by macro e.g. when anything changes or is updated.

Thanks in anticipation

Simon Clark

 

[zacemmel]

Function mycalc()
Dim calcState As Integer
Application.Volatile
calcState = Application.CalculationState
MsgBox calcState
If calcState = 2 Then
mycalc = "Manual"
End If
If calcState = 1 Then
mycalc = "Automatic"
End If
End Function

http://www.excelquestions.com
This message was edited by zacemmel on 2002-10-18 18:19

 

[zacemmel]

To answer your question, just use a UDF ( user defined function ) You can use UDFs to do a lot of tricky stuff. If you want it to recalc everytime the spreadsheet does, then use then Application.volatile method.

Hope this helps.

 

[simon clark]

Thanks for the response. Unfortunately i am not to intelligent when it comes to VB.
Please tell me exactly where to put the code. I stuck it in a module but nothing seems to be happenning in the spreadsheet when i alter cells, or turn recalc on or off.
I imagine i have to activate the "mycalc" function somwhow. How do i do this.
Simple steps please (i'm a beginner)!

Thanks

 

[Mudface]

For your second question, you can use several worksheet events to trigger your macro(s). As a simple (and annoying) example, right-click on your sheet tab, select View Code and copy and paste in the code below. Click on the right dropdown of the right-hand pane to see other events for which it is possible to write code.
<pre>
Private Sub Worksheet_Change(ByVal Target As Range)

MsgBox "You just changed " & Target.Address

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

MsgBox "You just selected " & Target.Address

End Sub
</pre>

 

[Mudface]

CalculationState isn't a valid property or method for the Application object in 97 or 2000, it may be in XP.

Right-click on your sheet tab, select view code, double-click on the Thisworkbook entry in the explorer pane and copy and paste in the following code. Every time you change a worksheet, the current calculation mode will be displayed in cell A1 of the active sheet-
<pre>
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim CalcState As Integer

CalcState = Application.Calculation
Select Case CalcState
Case xlCalculationAutomatic
Range("A1") = "Automatic calculation"
Case xlCalculationManual
Range("A1") = "Manual calculation"
Case xlCalculationSemiautomatic
Range("A1") = "Semi-automatic calculation"
End Select

End Sub
</pre>

 

[Yogi Anand]

Hi Simon Clark:

in reference to first part of your question ...
You could use the following lines of code that write the Calc stus in cell A1

If Application.Calculation = xlAutomatic Then
[a1] = "Calc set to Automatic"
Else: [a1] = "Calc set to Manual"
End If

in regard to the second question -- yes! you can have the code be triggered automatically, for example by a WorkSheet_Change event

Regards!

Yogi

 

[Legacy 11273]

Here's a UDF. The code could also be adapted for an event procedure.

Function mycalc()
Dim calcState As Integer
calcState = Application.Calculation
Select Case calcState
Case xlAutomatic: mycalc = "Automatic calculation"
Case xlManual: mycalc = "Manual calculation"
Case xlSemiautomatic: mycalc = "Semi-automatic calculation"
End Select
End Function


Here's another way (without VBA) :-

Define a Name - in the NamesInWorkbook box put CALC and in the RefersTo box put =GET.DOCUMENT(14)

Type =CALC in any worksheet cell and either 1, 2, or 3 will be returned representing :-

1 = Automatic
2 = Automatic except tables
3 = Manual

If required, a formula could be put in another cell to convert the numbers to the corresponding text.

 

[Paul B]

put this in a cell, will return manual or automatic
=INFO("recalc" )

 

[Legacy 11273]

On 2002-10-18 22:26, Paul B wrote:
put this in a cell, will return manual or automatic
=INFO("recalc" )

Nice one!