How to turn certain work sheets to manual calculation?

[jyriii]

Hello,

I have added two data analysis work sheets with multiple formulas to my work book. The calculation of these formulas is slowing down the whole work book. -I guess every time I make a change somewhere it updates the results.

I tried to change calculation to manual but that affects the whole work book... :x I want only the two data analysis sheets to work on manual calculation. -Others should work as they did before I added those analysis sheets.

I guess we need some kind of macro for this problem?

 

[Ahnold]

This is a kinda roundabout way. But it does seem to work. Reply back if it does the trick. Copy this into the workbook module.

Private Sub Workbook_Open()
    Application.Calculation = xlCalculationManual
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    ActiveSheet.Calculate
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    ActiveSheet.Calculate
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.Calculation = xlCalculationAutomatic
End Sub

And if you're going to have other workbooks open add this.

Private Sub Workbook_Activate()
    Application.Calculation = xlCalculationManual
End Sub
Private Sub Workbook_Deactivate()
    Application.Calculation = xlCalculationAutomatic
End Sub

You will have to close the book and reopen to initiate the code.

 

[jyriii]

Well,

Thanks for the tip Ahnold! :wink: I haven't tried it yet, but I guess your solution works so that it updates a certain work sheet when it's being opened? I will definetely try it if no better solutions come up.

But sometimes I select the wrong work sheets, and I wouldn't like to wait for the calculations to be done in that case... I would appreciate if anyone could come up with a solution where you could really turn certain work sheets (for example "sheet2" and "sheet3") to manual calculation.

You see, I was thinking about adding an update button on those analysis sheets of mine...

 

[Pekkavee]

Hello Jyri

Nice to see other Finns here too.
I hope you get the problem solved. Ahnold's was quite nice anyway and it certainly works.

Terveisin
Pekka

 

[Ahnold]

But sometimes I select the wrong work sheets, and I wouldn't like to wait for the calculations to be done in that case...

You could try this. Replace "Sheet1" and "Sheet2" with the names of the two sheets that you don't want to calculate in this code.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If Sh.Name <> "Sheet1" Or Sh.Name <> "Sheet2" Then
    ActiveSheet.Calculate
    End If
End Sub

 

[jyriii]

Nope,

I don't think we can solve this problem with Workbook_SheetActivate and Workbook_Activate... I think it gets a bit messy if the formulas are counted when I open different sheets. In my opinion the calculation should work as if the data analysis sheets weren't there...

Personally I don't know much (/anything) about macros. -But I was wondering if we could use only the following part. -Is it possible to define the two sheets there instead of the whole "Application"?

Private Sub Workbook_Open()
    Application.Calculation = xlCalculationManual
End Sub

 

[tusharm]

XL uses a fairly sophisticated algorithm to decide what to recalculate when. If it is recalculating those worksheets, it is because whatever you are doing makes XL believe it must. Bottom line, there is more to this than just turning off calculations for selected sheets.

Of course, XL is (usually) very cooperative about recalculating in the background. If you update a cell and it starts recalculating, anything you do, such as enter data in another cell, will cause XL to terminate recalculation.

Hello,

I have added two data analysis work sheets with multiple formulas to my work book. The calculation of these formulas is slowing down the whole work book. -I guess every time I make a change somewhere it updates the results.

I tried to change calculation to manual but that affects the whole work book... :x I want only the two data analysis sheets to work on manual calculation. -Others should work as they did before I added those analysis sheets.

I guess we need some kind of macro for this problem?

 

[jyriii]

Okay,

I believe in Excel's excellence, tusharm. But in this case the problem is quite simple, though. -And the situation just makes one wonder how hard it can be to turn off the automatic calculation on certain sheets... -I mean it should not be a problem if other sheets are not using the analysis' sheets results.

 

[FastExcel]

Tushar is correct that it is generally not a good idea to use sheet.calculate unless you are extremely careful about intersheet dependencies (because sheet.calculate ignores them so you may get incorrect results).

It is also possible to switch sheets off from automatic calculation using worksheet.enablecalculation, (This is what FastExcel uses internally for mixed-mode calculation) but you need to be careful about how you do this (the property is not saved with the workbook, it triggers a full sheet recalc and there are potential dependency problems).

 

[jyriii]

Thanks FastExcel, I got it to work now!


I placed the following code in the Workbook module to turn my Profit work sheet's calculations off:

Private Sub Workbook_Open()
    Sheets("PROFIT").EnableCalculation = False
End Sub

And then I placed this code for the Profit sheet's update button:

Sub ProfitUpdateButton()
    Sheets("PROFIT").EnableCalculation = True
    Sheets("PROFIT").EnableCalculation = False
End Sub

No problemos so far...
:wink: