Setting Global Recal

**nils.soderstrom** · 04-30-2010, 03:32 AM

Hi,

I've got a rather large spreadsheet with lost of calculations being performed. This spreadsheet works as a tracker for the project I'm on, and it is uploaded at our internal server. The problem is that when people are updating the worksheet, it prompts on recalcuationg the entire sheet everytime, something that needless to say, is very time consuming. What I want to do now is to globaly terminalte the recalcualtion setting so that it is only done manually for this worksheet. I know how to update these settings locally in Excel, but as I said, I need i done globally for this spreadsheet. There has got to be a marco for this, anybody got some input?

Tnx

**DonkeyOte** · 04-30-2010, 03:43 AM

If Auto Calculation grinds your file to a halt then chances are it needs a re-design - that's just my opinion of course.

You can use the Open event of the Workbook to enforce Manual Calc:

Please Login or Register  to view this content.

but as you can see from the above this is an App level setting rather than Workbook level so it will be applied to all files open in that instance of Excel.

It follows that per the above you may be affecting the integrity of other models being used by the user at that time - without their knowledge.

I would stand by the point that you need to revisit the model itself - ie improve calculation time (avoiding arrays, volatiles etc etc...)

**nils.soderstrom** · 04-30-2010, 03:58 AM

Tnx!

It's is not as much as it grinds it to a halt, rather calculates for a minute or two, enoying enough... The spreadsheet is only meant to be used as a reporting tool, so the users will just go in, update it and then close it down again. No real time spent working with it - that's my job

. So if it affects other sheets in the meantime, I dont really care, it woun't be for long anyhow.

**DonkeyOte** · 04-30-2010, 04:13 AM

I think the majority of the nerds amongst us would consider a re-calc time running into "minutes" as pretty bad

Sad to say that a lot us like a good optimisation challenge so if you ever feel the urge to see if the calculation time can be improved upon you know where we are !

**nils.soderstrom** · 04-30-2010, 05:46 AM

Sure thing! Any fast pointers?

What I'm doing in short is pulling a SUMPRODUCT() and COUNTIF() from a table of some 350 rows, and using this to populate another table from which I then create a graph. This is simple and fine at first, but when doing the same thing for 5 different tables at ones the thing starts to slow down. I'm going to post a sample worksheet I just have to prepare it first.

**DonkeyOte** · 04-30-2010, 05:58 AM

Fast Pointers ?

Avoid using Arrays & SUMPRODUCTs in large numbers and/or with large ranges.
If you do use them ensure they are non-Volatile whenever & wherever possible.
(you can often avoid the need Array / SUMPRODUCT requirement by virtue of helper calcs - more lightweight calcs will perform better than a smaller number of expensive calcs)

Try to sort data appropriately wherever possible so as to make use of Binary Search based lookups (rather than conducting exact match lookups) - this will greatly reduce the number of cells being processed in large data sets.

Charles Williams' site is invaluable on the subject of optimisation

Once you post your sample we can probably offer more specific pointers.

**nils.soderstrom** · 05-03-2010, 05:54 AM

Hi again,

I need to reopen this thread cause the code above doesn't solve all my problems. It turns out the darn thing still recalculates the entire worksheet when saved and when filtered. This is a major problem. Is there no why to just go:

Application.Calculation = NEVER!!!!! (unless I say so, by pressing F9 - darn machine)

Nope, this is not frustration me...

**DonkeyOte** · 05-03-2010, 07:32 AM

Originally Posted by nils.soderstrom

It turns out the darn thing still recalculates the entire worksheet when saved...

Recalculation upon Save is another App setting that can be controlled via Code, eg:

Please Login or Register  to view this content.

Originally Posted by nils.soderstrom

...and when filtered

Filtering should not invoke Calculation in Manual mode - altering row visibility is a Volatile action but in Manual mode nothing should recalculate per se.

Do you have any VBA events in place ?

To reiterate though - coding manual calc etc is (IMO) method of last resort, akin to rearranging the deckchairs on the Titanic - that is to say you would be best served spending time on those underlying issues that are making the use of Manual calc necessary in the first place.

We're still waiting to see the sample you spoke of ...