Solved

xlcalculationmanual in VBa

Posted on 2011-03-23
7
1,079 Views
Last Modified: 2012-06-22
Dear Experts,

I would like to have a good example website to show how it will have code speed advantage
for Application.Calculation = xlCalculationManual and Application.Calculation = xlCalculationAutomatic
I know those functionality but I wonder what situation we will use it individually  for speed ?

Aud during file save, Is it all formula in Excel sheet will be updated even I set it xlCalculationManual ?

Please advise

Thanks
Duncan l
0
Comment
Question by:duncanb7
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 20

Expert Comment

by:Ardhendu Sarangi
ID: 35199402
Hi,
if you search in experts-exchange, you can find good articles about this. THere is also a write-up on C Pearson's site.

Here's the link - http://www.cpearson.com/excel/optimize.htm

From my experience, the advantage to set it xlCalculationManual is if when your data is huge and there are a lot of formulas.

If the  xlCalculationManual is set to manual, it won't update on file save.

Let me know if you need any further clarifications,

Thanks,
Ardhendu
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35201760
I think formulas are recalculated before save

Saqib
0
 
LVL 13

Author Comment

by:duncanb7
ID: 35217957
If recaulation before save ,So it might not help me if my VBA code needs to do open file and save file for automation looping 100 files.

THe function might be good when you are doing a VB code debugging or doing a code for sepcifiy sheet from that you
don't want to recaulate other sheets since other sheets value is not important at this moment

AM i right ?

Please advise

Duncan
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 20

Expert Comment

by:Ardhendu Sarangi
ID: 35218094
Hi Duncan,

If you use the following code -

Private Sub Workbook_Open()
Application.Calculation = xlCalculationManual

Application.CalculateBeforeSave = False
End Sub

Open in new window


then this doesn't recalculate while saving.

Application.CalculateBeforeSave = Values are True or False. If calculation is Manual and CalculateBeforeSave is true when you Save the workbook it will be recalculated.
0
 
LVL 13

Author Comment

by:duncanb7
ID: 35218169
Dear  pari123,

Thanks for your suggestion ?

But my question, even I don't know

In what situation, xlcalcaulteManual is good for speed optimization  ? or what Is said above is correct  
(THe function might be good when you are doing a VB code debugging or doing a code for sepcifiy sheet from that you
don't want to recaulate other sheets since other sheets value is not important at this moment )

In what situation, Application.CalculateBeforeSave = False,is good for speed optimization  ?




0
 
LVL 20

Accepted Solution

by:
Ardhendu Sarangi earned 500 total points
ID: 35218237

>> In what situation, Application.CalculateBeforeSave = False,is good for speed optimization  ?

As I mentioned before, it would really make a difference if you have a file with huge volume of data and a lot of formulas. It really differs from sheet to sheet. Say for example, more than 1000 rows of data and 3 columns have formulas in it...so there are 3 * 1000 = 3000 formulas that excel needs to calculate would obivously slow down editing the spreadsheet.

but without looking at the data, you need to make an educated guess on it...
0
 
LVL 13

Author Closing Comment

by:duncanb7
ID: 35221956
Thanks for your reply,
It understood more
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question