Formula speed analysis

[Glenn Kennedy]

Time to learn some new tricks.... before it's too late.

What are your preferred methods to analyse the processing time taken by individual formulae in Excel. I've footered around with a few things that i found on the Interweb... but with no great success. However, anyone who knows me here, will also know that VBA is .... ahem!! ... not one of my strengths. Very recently, I have developed a desire to be able to evaluate the speed of different formulae that do the same thing... The exact nature of the formulae is irrelevant - it's the process that I want to understand.

Some of you will have learned all this from your mother's milk (to coin a phrase - and just maybe a nicer one than some of Trump's). I didn't.

BUT.... please remember that I use VBA like a drunk uses a lamp-post: for support, not for illumination... so you just might have to take some baby-sized steps with me!!

[shg]

Here's what I use, Glenn.

[Glenn Kennedy]

Thanks. i think I've been able to get it working for me. I'll shout if I hit a snag.

[shg]

You're welcome, Glenn. The narrative could be improved.

[shg]

Improved instructions.

EDIT: A little more cleanup.

[Glenn Kennedy]

I've had a chance to try it out "for real" today. It does exactly what I had hoped it would do. Thanks again..

[shg]

I'm pleased, Glenn, you're welcome.

[nvu19991]

@shg: Can you modify the file so it could check the entire workbook? But not in-depth analysis.

I receive a fairly complex workbook and it got to the point that doing certain things become very slow. I would like to optimize the responsiveness of the workbook. I think I tried as many ways to improve as I know. That including replacing all volatile functions, using dynamic ranges, removing external references, etc. But I don't think I could cover the workbook completely as it's fairly complex and it's done by someone else.

I think of a macro that run every non-empty cell once in a workbook and return in a new sheet that has how much time need to calculate a cell for every cell. That would point out which cell/formula should be improve. It doesn't have to check for consistent/grouping of formulas, it doesn't have to care which cell has what formula at all.

I think it would be a great as it cover the width of the workbook, just as useful as analyze the depth of it.

[FDibbins]

Great stuff Steve, I have saved this (with your name attached) in my "keeps" folder

[Glenn Kennedy]

I think it's great. It does exactly what it says it does... Sometimes if you select too many rows, it "throws the head up" and refuses to play, but....