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

simon clark

New Member
Joined
Oct 14, 2002
Messages
29
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

zacemmel

Well-known Member
Joined
Apr 29, 2002
Messages
636
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

Well-known Member
Joined
Apr 29, 2002
Messages
636
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

New Member
Joined
Oct 14, 2002
Messages
29
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

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454

ADVERTISEMENT

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

Code:
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
 
L

Legacy 11273

Guest
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

Well-known Member
Joined
Feb 15, 2002
Messages
575
put this in a cell, will return manual or automatic
=INFO("recalc" )
 

Forum statistics

Threads
1,148,172
Messages
5,745,176
Members
423,931
Latest member
thangvan114

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top