disabling automatic calculation

erniepoe

Active Member
Joined
Oct 23, 2006
Messages
339
Office Version
  1. 365
Platform
  1. Windows
Hi all, I have a huge database that I'm working with, and worksheets within the same workbook that run a lot of sumproduct formulas, so any filtering/updating within the database took a long time.


I installed the following code (in the right-click, view code) window, which I thought would turn the specific sheet into manual calculation, but also make it so that other Excel workbooks could be left in automatic calculation mode without having to physically change it back.


Is this a matter of the macro not be sophisticated enough, or am I installing/running it incorrectly? What is happening is that it still turns this and every workbook into manual calculation mode, when I only want it on one specific work book.


Thanks,
Ernie



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

Private Sub Worksheet_Deactivate()
Application.Calculation = xlCalculationAutomatic
End Sub
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,122
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. Mobile
  3. Web
Calculation is an application level thing. In order to switch back on the calculation when switching between workbooks you need to use a similar code in the ThisWorkbook module using Workbook_Activate/Deactivate events
 

erniepoe

Active Member
Joined
Oct 23, 2006
Messages
339
Office Version
  1. 365
Platform
  1. Windows
Thanks Dave,

So that would be a matter of just right-clicking on the tab in question, clicking on the "this workbook" thing over on the project VBA tree in the upper left, and copy-pasting the code in there?
 

kpark91

Well-known Member
Joined
Jul 15, 2010
Messages
1,582
Yes. pretty much.

But don't forget to change the Sub names.

from Worksheet_Activate to Workbook_Activate
 

erniepoe

Active Member
Joined
Oct 23, 2006
Messages
339
Office Version
  1. 365
Platform
  1. Windows
Awesome. Thanks so much, guys.
 

erniepoe

Active Member
Joined
Oct 23, 2006
Messages
339
Office Version
  1. 365
Platform
  1. Windows
Also, should I change the calculation to manual in the workbook in question, or just leave it all as manual and have the macro take over?
 

erniepoe

Active Member
Joined
Oct 23, 2006
Messages
339
Office Version
  1. 365
Platform
  1. Windows
hmm, it didn't really work. the calculations are still automatic even with the code installed, so it runs slowly.

if I change it back to manual calculation, it makes that change in every excel spreadsheet.


back to the drawing board, i guess....
 

kpark91

Well-known Member
Joined
Jul 15, 2010
Messages
1,582
LOL. Ohhhh I just read your question right now -_-
I just just caught up with all the replies put on this thread..
You don't need to put the code in Workbook Module

So the the original code you were using (Worksheet_Activate) WAS CORRECT.

To speed up the calculations, an easy way to do it is to disable ScreenUpdating and enabling ScreenUpdating after the calculation is done.

so...

Code:
Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = True
End Sub

Private Sub Worksheet_Deactivate()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
 

erniepoe

Active Member
Joined
Oct 23, 2006
Messages
339
Office Version
  1. 365
Platform
  1. Windows
Private Sub Workbook_Activate()
Application.Calculation = xlCalculationManual
End Sub

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


---------------------------


This is now the code I have in the "this workbook" module. It is correct, no?
 

Forum statistics

Threads
1,176,151
Messages
5,901,642
Members
434,909
Latest member
subratgupta

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