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!

 

[Andrew Poulsom]

In what way doesn't it work?

 

[RoryA]

From a quick glance it's only the sort part that is 2007 specific. Try changing this:

With rAll.Worksheet.Sort
.SortFields.Clear
.SortFields.Add Key:=rKey, _
SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal

.SetRange rAll
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

to this:

rAll.Sort Key1:=rKey, Order1:=xlDescending, _
DataOption1:=xlSortNormal, Header:=xlYes

 

[matthew.armitage]

I get the message

Compile Error:

Method or Data Member not found

It stops on the line:

With rAll.Worksheet.Sort

Thanks

 

[Jonmo1]

Confirming Rorya's Post.

I got the same error you did when I ran it.

And made the changes Rorya Suggested

Runs fine now.



This is actually pretty cool and handy code to keep around..

 

[matthew.armitage]

Yes its working as we speak. Going through my sheet really slowly actually.

It will help me a lot. I usually go through my sheet pasting as values 'trial and error' LOL

Matt

 

[gino59]

Got to agree with jonmo1 - this is seriously slick and amazingly valuable! I'm running it now on a workbook I had abandoned because it was choking along (taking more than a couple of minutes to calculate)!

Ya just gotta love this site!

Cheers!
Gino

 

[FastExcel]

There is free code you can use to identify slow formulas in my MSDN article on improving Calculation performance at
http://msdn.microsoft.com/en-us/library/ff700515.aspx

The 3 MSDN articles and my website provide a methodology for finding and removing Excel calculation bottlenecks.
http://msdn.microsoft.com/en-us/library/ff726673.aspx
http://www.decisionmodels.com/

FastExcel is a much more comprehensive set of tools to help you speed up and clean up your workbooks, but it's not free.

 

[crook_101]

Hi,

Gotta agree, this is a very handy piece of code, will be storing this one away

Ian.

 

[a13ks]

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!!!