How to enable or disable costly computing in an Excel spreadsheet?

Question

I use huge excel sheets and they load forever due to cell calculations using macros and other formulas (more than 5 minutes, even if I have a good computer). I was wondering if there is a way to save excel files with current cell values ​​instead of calculating cells every time I open the file.

What I'm looking for is like a switch that turns calculations on and off, so that when I need to use them, I could turn them on, and when I finished, I could turn it off, and the cells will keep their current values.

Maybe I could create a macro that would do something like this, or maybe I just dream and there is no other way, so I just have to sit and wait.

Answer 1

We have a similar problem.

This is what we use:

Function TurnOfCalcs() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Application.EnableEvents = False Application.DisplayAlerts = False End Function 

We turn off calculations, shielding, alerts, and events while loading and updating raw data.

Once the streaming data from the sheet is finished, we turn on the updates again:

 Function TurnOnCalcs() Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic Application.EnableEvents = True Application.DisplayAlerts = True End Function 

You still have udpate time, but that means you are not updating after every change to one cell, which should dramatically speed up the file download time.

Answer 2

You can set calculation parameters for Excel both through the graphical user interface and through VBA: Application.Calculation = xlManual

Here's more info ... http://www.decisionmodels.com/calcsecretse.htm

Answer 3

Using:

 Private Sub Workbook_Open() Module1.TurnOff End Sub 

Then click the button to which the macro is assigned.

 Sub Button1_Click() If Application.EnableEvents = True Then TurnOff Else TurnOn End If End Sub 

Then we get this in the module:

 Public Sub TurnOn() Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End Sub Public Sub TurnOff() Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End Sub