I have an excel file with 10 sheets, Data and formulas occupying 95,000 rows, 100 columns in each sheet , and I'm having a hard time opening, refreshing and updating because it's too slow.
I'm using excel 2007
Questions:
1. How will I be able to make my file faster whenever I update it?
2. Is there a difference in speed if I just create a VBA code for the formulas instead of creating formulas in each cell?
Thanks!
Its so slow because when you open the workbook Excel is calculating every formula. Look in the help file for recalculation. You should see how to set the recalculation off in the workbook open event in VBA.
Peace of mind is found in some of the strangest places.
Put this is a sun in the workbook open event
[VBA]Sub TurnOffCalc()
Application.Calculation = xlManual
End Sub[/VBA]
Peace of mind is found in some of the strangest places.
questions:
1. Will I put the code in every sheet?
2. If I'll turn-off calculation, how will I run the calculation manually?
3. Or can I just create a calculation VBA code so I'll just run the code when I need to calculate cells?
4. will this make my updates faster?
1. In VBA editor look in the top of the screen. There should be 2 drop down arrows. Select from the Workbook Open. Place the code in there. It will create a sub for you so the only line you will need from above is the
[VBA]Application.Calculation = xlManual [/VBA]
This controls the all sheets in the workbook.
2. See 3 for answer
3. Yes
4. Yes
Peace of mind is found in some of the strangest places.
Okay, thanks!
i'll start doing this!
I tried to do this but, everytime I run the code in sheet 1, the new data that I input in the file doesn't calculate. And it doesn't calculate anymore after I deleted the code.
[vba]Application.Calculation = xlAutomatic
[/vba]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Will this code automatically calculate my fiel upon opening it?
Coz, I'm looking for a way to stop the calculation upon opening it and a VBA code that I can run when I need to calculate the formula already.
Is there a way? Is it a long code? or I can just have a short code such asapplication.calculate=xlautomatic
Hi generacela,
Is it the formula the same in all cells. It may be better for you to use a button with VBA code to calculate the values. Then you will be able to update the sheet and only thjen calculate the values.
You set it to calculation to Manual as post 3
You can force an update using the command Calculate
You reset to "normal" operation as Post 9
Look at Calculation in VBA Help for more info.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Okay, i got it! Thanks!!!!
ps. how will I mark my solved threads "SOLVED"?
Thread Tools dropdownOriginally Posted by genracela
Okay, i got it! Thanks!!!!
ps. how will I mark my solved threads "SOLVED"?
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
goodness! been searching since day 1, and it's just under my nose!
Thanks for all the help mdmckillop!!!
Posting Permissions
Reply With Quote
