PDA

View Full Version : Solved: Questions regarding Excel vs VBA



genracela
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

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!

austenr
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.

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

genracela
04-21-2010, 05:43 PM
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?

austenr
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

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

i'll start doing this!

genracela
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.:(

genracela
04-21-2010, 10:18 PM
:(

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

genracela
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
application.calculate=xlautomatic
:dunno

arangogs
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.

mdmackillop
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.

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

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

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

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

genracela
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: