turn off Auto-calculation when launching an xls file

[kwagner1]

I know i can manually turn off auto-calculation when i start Excel (via Tools --> Options --> Calculation --> manual).. BUT, what i need to figure out is how do i prevent auto-calculating when I double-click a ".xls" file from my file system and then Excel launches??
I have a large file that has many (thousands) of Vlookups and when i forget to open Excel and change the option first, i end up waiting.... and waiting....

Is there any way to use VB code to disable auto-calc when I launch Excel by double-clicking my file from the file system?

 

[golfbum71]

Are you open to a different method

kwagner1,

Would you consider putting a "WorkbookOpen" event in to the large file?

This would turn the calculation to manual automatically when you open the document...

Putting this in the "ThisWorkbook" Object:

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
    With Application
        .Calculation = xlManual
        .MaxChange = 0.001
        .CalculateBeforeSave = False
    End With
End Sub

It will stop the calculations both before it opens and before it saves...
You'll always have the "F9" option when you have it open...

Then you don't have to code other excel documents or make an xla file. (I've never made one...)

I know it's not exactly what you are looking for, but it's a thought.

Cheers,
Geoff

 

[xenou]

I was under the impression that the calculation takes place first, then the open event...in which case, no help.

So, I am interested to know the result here.

BTW, if the links are in other workbooks and the other workbooks are closed, and you set Edit | Links to not update when opened, this can have a positive effect. Be careful with manual settings if you share files since sometimes you may propagate those settings to co-workers unintentionally ...in some cases really confusing them.

 

[kwagner1]

thanks guys!... Alexander, good point - the workbook will be shared with co-workers, so maybe I need to rethink my approach. It appears as though the setting changes are at the application level (i.e. Excel) rather than at the workbook level - any thoughts on how to do the same thing at the workbook level (or is it just not possible)??

thanks!

 

[golfbum71]

Hmm...I'm just spitballing now since I didn't know about the Calc before running macros thing...

Could you combine the two ideas?

Set the links so they don't update when opened, then have a reminder msgbox open when the sheet opens reminding them to turn on the calculations for the lastest information when they are ready...and have a button on the page that asks if they'd like to update the information--and turns the update on.

And to make sure that the settings don't stick you could have a before close event that checks the status...

It doesn't sound like a pretty project, but I can't come up with a better idea. I look forward to any other inputs.

Good luck,
Geoff

 

[xenou]

http://www.ozgrid.com/forum/showthread.php?t=23813
This thread makes it sound like an open event is possible - if the linked workbooks are closed.

http://www.decisionmodels.com/calcsecretse.htm
This is the best explanation I have yet found of calculation...the funny thing is that the settings are saved with the workbook, but Excel will use the calculation settings of the first workbook opened, regardless of what is saved in other workbooks. Still, in my experience, you can end up saving manual settings without realizing, and later opening these files and getting really confused as to what is going on or how to stop it. The site, by the way, seems to offer software designed to give more control over calculation. I have no experience with or connection to this product.

The upshot is that closing linked workbooks and not updating links will help, first of all, and then using manual before or when opening links will be a means of exercising control. You may want to create a custom shortcut to turn manual on and off quickly - using the following macro and assigning a shortcut key. I'd also recommend a routine to set calculation back to automatic on close, and remember that if you open other workbooks with calculation in manual these workbooks will become manual too!! That's why it gets tricky. Personally, I've exercised patience so far, although I have a few workbooks that take about 30 seconds to open. If I do use manual, I try to think about what other workbooks I would be spreading the setting too...and trying not to save those settings unintentionally.

Sub TurnCalculationOnAndOff()
    Select Case Application.Calculation
    
        Case xlCalculationManual
            Application.Calculation = xlCalculationAutomatic
        Case xlCalculationAutomatic
            Application.Calculation = xlCalculationManual
        Case xlCalculationSemiautomatic
            MsgBox ("Calculation is set to semiautomatic. No change will take place")
        End Select
        
End Sub