Identifying which formulas in Excel are slowing down workbook recalaculation

[matthew.armitage]

http://www.eggheadcafe.com/tutorial...are-slowing-down-workbook-recalaculation.aspx

I have been trying to use the code shown in the link above. It looks great and perfect for what I want to do.

However it does not work, I prusume it is 2007 only....

Does anyone know of something simalar that will work in 2003?

Thanks!

 

[Aladin Akyurek]

As a beginner with regards to VBA could some please be so kind and help me execute this code. I have a rather formula intensive workbook which has had a major update recently and has become horrendously slow to respond making it almost unworkable.

I have had some success in speeding it up slightly but still not enough by updating and tidying up conditional formatting, Vlookups are now Index Match, fine tuned other formulae, removed unnecessary calculations etc etc.

Help!!!

Get rid of all conditional formatting (especially when it involves many cells).

VLOOKUP >> INDEX+MATCH should not differ in performance. A better performance is guaranteed if the match-range can be set in ascending order...

Avoid volatile functions like OFFSET, INDIRECT, etc.

Try to switch if poassible at all from array-processing formulas (this includes also the SUMPRODUCT formulas) to range-processing formulas like SUMPRODUCT >> SUMIFS, SUMPRODUCT >> COUNTIFS, etc.

Avoid computing the same thing multiple times in the same formula.

 

[a13ks]

Thanks Aladin,
I have stripped back a lot of the formulae and conditional formatting already and am not using any volatile rules.
The index match function actually helped immensely with regards speeding up the processes, just not enough. It's the lag after simply inputting some data and then waiting. It was approx 4 to 5 secs originally but have got it down to 2 secs.
What I would love to do is to run the code in this thread to see what it would highlight.
My sheets are used in a live manufacturing environment hence it needing to be usable and not lagging when needed.
My original docs pre-update were approx 1.8mb, this went upto 5.7mb after the initial update. I have managed to reduce this down to 3.7mb. One of the reasons this increased so much is an index match formula that returns and image (1 of 16 variants). This is set as a default value and will change occasionally but the image (which was originally manually copied & pasted) is required for setting up presses.

Sorry for going on but thought I'd offer a little more detail.

 

[Aladin Akyurek]

Care to post an index/match formula you invoke the most frequently?

How about the array-processing formulas?

 

[a13ks]

This one is regarding the image mentioned:
=INDEX(DATA!$Z$177:$Z$207,MATCH($W$29,DATA!AA177:AA207,0))


=IF($AI$42=0,0,(IF($AI$42=84,84,(INDEX(DATA!$N$4:$N$74,MATCH($AI$42,DATA!$M$4:$M$74,0))))))

=IF($C$2="Saturday",DATA!H39,(IF($C$2="Sunday",DATA!H53,DATA!H11)))

=IF(C2>$N$30,INDEX($F$31:$F$34,MATCH(C2-1,$N$30:$N$34,1))&C2-LOOKUP(C2-1,($N$30:$N$34)),"")

=IF(T6=0,0,(IF(S6="C",(INDEX(DATA!$C$88:$C$97,MATCH(T6,DATA!$A$88:$A$97,0))),(INDEX(DATA!$B$88:$B$97,MATCH(T6,DATA!$A$88:$A$97,0))))))

To list a few.

 

[Aladin Akyurek]

@ a13ks

I don't think these formulas are that costly, unless you have zillions of them.

​

 

[a13ks]

Thatswhat I thought. There are a fair few of them but definitely not hundreds.
My belief is that there is some rogue formulae hidden somewhere from oast updates and movement over the years going undetected.
Coukd you please help me execute the code in this thread as I am a total novice using VBA.
I copied the code and created a module ( I think) as is explained but have no idea what to do next.