How can I enable and disable expensive calculations in an Excel spreadsheet?

Question

I use huge excel worksheets and they take forever to load because of cell calculations using macros and other formulas (over 5 minutes even if I have a good computer) . I was wondering if there was a way to save the excel files with the current cell values instead of calculating the cells each time I open the file.

What I am looking for is like a switch that would turn the calculations on and off so that when I need to use them I could set them to on, and when I am done, I could switch it to off and the cells would keep their current values.

Maybe I could create a macro that would do something like that, or maybe I am just dreaming and there is no other way around, so I should just sit and wait.

chollida · Accepted Answer · 2013-06-12 16:03:50Z

2

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, screenupdating, alerts and events while the initial data is loading and updating.

Once the streaming data from the sheet has finished we turn updates back on like so:

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

You still have the udpate time but this means you don't' do updates after each single cell change, which should dramatically speed up your file loading times.

answered Jun 12, 2013 at 16:03

chollida

7,91412 gold badges57 silver badges86 bronze badges

Out of curiosity why wouldn't you want alerts or screen updating? I would never want to turn of either of those during normal use. Defiantly while running code but never during normal use.
– user2140261
Commented Jun 12, 2013 at 16:08
Our use case is a bunch of RTD data that come in on sheet startup. We don't care about the screen updating or alerts at that point. We just want the data to come in first. This step alone can take 30 seconds. Once the data is in then we turn on calcs as shown above and the sheet cranks away for another 30 seconds. With the calcs on the whole operation takes 5 minutes instead of 1. As to why turn off screen updates and Events, we've noticed that they noticeably slow down the initial loads by a good 50% for our use case, YMMV:)
– chollida
Commented Jun 12, 2013 at 16:13

Add a comment |

Steph Locke · Accepted Answer · 2013-06-12 16:01:20Z

0

You can set the calculation options for Excel both via the GUI and via VBA: Application.Calculation = xlManual

Here is some more info... http://www.decisionmodels.com/calcsecretse.htm

answered Jun 12, 2013 at 16:01

Steph Locke

6,1964 gold badges43 silver badges79 bronze badges

Add a comment |

user2140261 · Accepted Answer · 2013-06-12 16:10:44Z

0

Use:

Private Sub Workbook_Open()
Module1.TurnOff
End Sub

Then have a button than is assigned this macro

Sub Button1_Click()

If Application.EnableEvents = True Then
    TurnOff
Else
    TurnOn
End If

End Sub

Then have this in a module:

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

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

edited Jun 12, 2013 at 16:10

answered Jun 12, 2013 at 16:03

user2140261

8,0037 gold badges35 silver badges45 bronze badges

Add a comment |

Collectives™ on Stack Overflow

How can I enable and disable expensive calculations in an Excel spreadsheet?

3 Answers 3

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Your Answer

Sign up or log in

Post as a guest

Related