View Full Version : Solved: Questions regarding Excel vs VBA

04-21-2010, 04:37 PM
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


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?


04-21-2010, 04:55 PM
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.

04-21-2010, 05:01 PM
Put this is a sun in the workbook open event
Sub TurnOffCalc()
Application.Calculation = xlManual
End Sub

04-21-2010, 05:43 PM

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?

04-21-2010, 05:51 PM
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

Application.Calculation = xlManual

This controls the all sheets in the workbook.

2. See 3 for answer
3. Yes
4. Yes

04-21-2010, 05:55 PM
Okay, thanks!

i'll start doing this!

04-21-2010, 07:45 PM
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.:(

04-21-2010, 10:18 PM

04-22-2010, 12:23 AM
Application.Calculation = xlAutomatic

04-22-2010, 12:29 AM
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 as

04-22-2010, 01:21 AM
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.

04-22-2010, 01:23 AM
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.

04-22-2010, 04:45 PM
Okay, i got it! Thanks!!!!:yes

ps. how will I mark my solved threads "SOLVED"?

04-22-2010, 05:03 PM
Okay, i got it! Thanks!!!!:yes

ps. how will I mark my solved threads "SOLVED"?
Thread Tools dropdown

04-22-2010, 05:08 PM
goodness! been searching since day 1, and it's just under my nose!:doh:

Thanks for all the help mdmckillop!!!:bow: