-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Application.Calculation = xlManual
- Thread starter Tonto111
- Start date
Corticus
Well-known Member
- Joined
- Apr 30, 2002
- Messages
- 1,579
Hi,
It is good to set calculations to manual if you have a large spreadsheet with lots of formulas, it keeps the thing from recalculating all those formulas when you change anything, which can get very slow.
You force the calculation with F9
HTH,
Corticus
It is good to set calculations to manual if you have a large spreadsheet with lots of formulas, it keeps the thing from recalculating all those formulas when you change anything, which can get very slow.
You force the calculation with F9
HTH,
Corticus
Upvote
0
I would only use it if you have a lot of data to write to a sheet.
If this is the case, in the procedure before the data writing starts set it to manual and afterwards revert it back to auto.
It can be quite a pain if you forget that excel is in manual calc mode
If this is the case, in the procedure before the data writing starts set it to manual and afterwards revert it back to auto.
It can be quite a pain if you forget that excel is in manual calc mode
Upvote
0
Damon Ostrander
MrExcel MVP
- Joined
- Feb 17, 2002
- Messages
- 4,239
Hi Tonto,
Yes, Corticus is right on. I just want to add that when you write a macro that updates a worksheet containing formulas it is a good idea to turn calculation off at the beginning and back on at the end so that worksheet formula updates are not done repeatedly as the macro changes the worksheet, but rather just once at the end.
Yes, Corticus is right on. I just want to add that when you write a macro that updates a worksheet containing formulas it is a good idea to turn calculation off at the beginning and back on at the end so that worksheet formula updates are not done repeatedly as the macro changes the worksheet, but rather just once at the end.
Upvote
0
Andrew Poulsom
MrExcel MVP
- Joined
- Jul 21, 2002
- Messages
- 73,092
You can save your workbook with calculation set to manual. If that's the only workbook you ever open and you never change the setting then you don't need any VBA.
But if you open it on top of a workbook with calculation set to automatic it inherits the automatic property, so you need VBA to reset it to manual.
Mostly I use manual calculation. That's the setting in my Book and Sheet templates so new workbooks have it too. Excel tells me if I need to calculate and I rarely forget. On anything other than a small spreadsheet automatic calculation just slows the data entry process down.
But if you open it on top of a workbook with calculation set to automatic it inherits the automatic property, so you need VBA to reset it to manual.
Mostly I use manual calculation. That's the setting in my Book and Sheet templates so new workbooks have it too. Excel tells me if I need to calculate and I rarely forget. On anything other than a small spreadsheet automatic calculation just slows the data entry process down.
Upvote
0
DonkeyOte
MrExcel MVP
- Joined
- Sep 6, 2002
- Messages
- 9,124
Also - (my pennies worth)...
If you have multiple sheets and some parts of the macro depend upon calculated fields on certain sheets then Manual Calculate is still a must BUT remember the useful code that is:
Sheets("Sheet1").calculate
So rather than hitting F9 or having to use Application.Calculate in VB you can calculate individual sheets as your Macro progresses (as and when required).
I find that it's also a very useful macro to have if I am working on a massive spreadsheet - or creating one and you just want to calculate one sheet as opposed to hitting F9.
Anyway...that's my bit over.
Luke
If you have multiple sheets and some parts of the macro depend upon calculated fields on certain sheets then Manual Calculate is still a must BUT remember the useful code that is:
Sheets("Sheet1").calculate
So rather than hitting F9 or having to use Application.Calculate in VB you can calculate individual sheets as your Macro progresses (as and when required).
I find that it's also a very useful macro to have if I am working on a massive spreadsheet - or creating one and you just want to calculate one sheet as opposed to hitting F9.
Anyway...that's my bit over.
Luke
Upvote
0
Similar threads
- Solved
