Excel runs slow/long re-calculate times

[PhantomOak]

I changed a workbook I have to hold several hundred (maybe 200-300) Array Foruma.

It worked fine on friday, and now it is taking a god aweful amount of time every time I enter a value in any cell. I have never had a problem before with any workbook, even with MUCH larger number of forumlas.

Also, somehow it is accross most of my workbooks, only the simplest are spared.

Moreover, it will reach "100%" and then start again, sometimes going to 5+ itterations, even in workbooks that only have Pivot Tables as the complicating factor.



It does this on several work stations.

I would post my file, but this site does not let me.

 

[Trevor G]

Switch auto calulate off and when you want it to calculate then press F9, check your settings for the auto calculate

 

[Aladin Akyurek]

Care to post some example array formulas you have? Also, what version of Excel do you work with?

 

[PhantomOak]

The arrays ALL look like "iferror(Vlookup(X,X:X,X,False),"") or "if(Vlookup(etc)="Whatever",vlookup(etc),"")

I THINK I found the problem being the line in Private Sub Workbook_Open()

Application.Calculation = xlAutomatic

What I intended to do was prevent the workbook from opening in manual (because it confuses everyone and the workbooks are not that large). What is seems to do is recalculate the workbook every time anything cell changes.

What are some commands in VBA can I use to recalculate(say, at the end of a sub). Ie, claculate one time, calculate one specific sheet, calculate sheet on open, etc...

 

[Trevor G]

In the workbook event for on open place in this

Application.Calculation = xlManual

Behind each worksheet you can view the code and use Activate to then do the

Application.Calculation = xlAutomatic

And you can add that line after it runs a sub routine.