Turn off automatic calculation on startup

[vestax22]

Hi, I have developed an application using Excel 2000 and VBA Macros. This application is fairly large and takes some time to open. I noticed that on open there is a recalculation that I would like to turn off to save time. Does anybody know how to turn off this automatic calculation on startup?

Don't get me wrong I don't want to disactivate automatic calculation of formulas. I just want Excel not to recalculate on startup. Is this possible?

Thx

 

[RobBroekhuis]

Does the workbook you're opening have worksheet formulas with function calls to functions in a different workbook (for example, an AddIn)?


Rob

 

[vestax22]

No it does not. I contains formulas calling functions from within a module. Could that be the cause?

Thx

 

[Bowers74]

Hi vestax22,

assuming that the sheet containing the formulas is Sheet3, you could enter the following code into the Workbook_Open event of your Workbook:

Private Sub Workbook_Open()
Sheet3.EnableCalculation = False
End Sub

You will have to create a macro that turns calculation back on though, or else your application will no longer work.

Create a button and connect it to the following macro:


Sub Calculation_On()
Sheet3.EnableCalculation = True
End Sub

Hope this helps!



If you can't be "The Best", be the best at what you can!!!

Never say Never!!!
Nothing is impossible!!!

 

[RobBroekhuis]

I don't often use custom worksheet functions, but I know calls to external functions will trigger a recalc on opening. Do your custom VBA functions have application.volatile?


Rob

 

[vestax22]

Hey

Whats application.volatile?

 

[RobBroekhuis]

See VBA help - it describes the method better than I could, but I gather from your response that you don't use it.
I played around a little bit, in a blank workbook where I created a VBA function:

Function random() As String
random = Rnd(100)
End Function

I entered into cell A2: =random()+A1

Any time I change the value in A1, A2 gets recalculated, with a different random value. Changes elsewhere in the spreadsheet don't trigger recalculation (this changes when application.volatile is invoked). I then saved the workbook, noted the current value in A2, exited, and re-opened. The value in A2 had not changed, so no recalc was triggered upon opening. I'm not sure what it is about your workbook that causes recalculation on first opening. Do you use functions from Excel standard AddIns (such as analysis toolbox)?


Rob

 

[vestax22]

Well actually I use some sort of addin I guess

its calle DatePicker

its a sort of combobox that shows a calendar. Could this be the problem?


Thx for the replys

 

[RobBroekhuis]

I wouldn't think DatePicker (which is more of a custom control than an AddIn) would have anything to do with it, but you could try removing it temporarily, to be sure.


Rob