Calculation Speed Help

[sriche01]

Hello all,

I have been hesitating to ask for help on this question because of its convoluted nature.
I work in a macro enabled workbook with 32 worksheets, most of which are interconnected. 6 or 8 have worksheet code, plus I have 4 modules of code.

Calculation speed has often been a struggle. But it took a dive around the middle of last month. It went from about 5 seconds calculation to over 13. This bogs everyone using it down.

I have an addin that calculates speed for the workbook, a sheet, and a selected range. I've tried to use this to narrow down what is slowing things down.
I have tried to find any changes made around the time the calculation jumped, and am still looking, but nothing jumps out at me.
I have made a copy, deleted all code, all conditional formatting, even eliminated some sheets just to see if I could find the drag on speed, but still nothing.

So here's my question. Do any of you know of an addin or macro that can scan a workbook and find slow-to-calculate ranges? Or perhaps the ranges that need recalculating the most because they are over-referenced?

I know this is a shot in the dark, but any lead at this point would help!

Thanks!

 

[JackDanIce]

Stream line your calcs, check for unnecessary steps and use of Volatile functions (e.g. NOW, OFFSET etc) as these and any dependent cells all recalc each time a calculation is made.

Are you passing entire columns e.g. A:A as range arguments or fixed ranges, e.g. $A$1:$A$10000
Are they calcs you can perform in memory and output as values (not formula) to save unnecessary recalcs within the sheet(s)?
Are you turning calculation mode to xlManual before you run your code, then back to xlAutomatic near end?

I'm not sure of an addin that can explicitly evaluate your spreadsheet to that extent, it sounds more like a redesign exercise to identify cause(s) and resolve.

 

[sriche01]

Jack,
Thanks for your reply. I'm not sure that a total redesign is an option, although I will look at applying your suggestions as possible. The thing is, it changed suddenly, without the major addition of a worksheet or formula that is obvious. So major workbook functionality and speed were good before the middle of march. I still have a backup copy from the time period that calcs at 4.8 seconds. But in reviewing that copy versus the current copy, I'm struggling to see what changed structurally. The amount of actual data doesn't swing that much. So it has to be something I changed to process the data. That's where I need an analysis tool...

 

[Yongle]

not answering your specific question but may be a pointer for you

Array formulas can be resource-heavy because of the amount of work they are doing
Are there any array formulas anywhere in the workbook ?
Ensure that they are not evaluating whole columns when they could be evaluating much smaller ranges

 

[JackDanIce]

When I switched to Office 365 a macro I'd written, previously running fine, suddenly slowed down incredibly.

Searching online there's been comments about other people experiencing this. Not sure how I resolved but problem's gone away, or maybe a background update I didn't notice.

If you're processing data, again depending on "what" it is probably faster to do this in memory vs on the sheet

 

[NdNoviceHlp]

Just as an aside, it seems like I've read quite a few posts in forums very similar to yours where everything was working well using 365 until recently and then some update occurred and now everything is either slow or doesn't work. Some of the problems have been remedied by making sure all VBA has specific references ie. don't use Range("A" & 1) but instead ensure that it is specific Sheets("Sheet1").Range("A" & 1) Not sure if this helps? Dave

 

[sriche01]

As to 365, I run everything on 365. My copy from March still calculates at 4.8 seconds on 365. December's copy, (previous to substantive changes) calculates under 4 seconds. The current copy in use calculates at 13.5 seconds. All on 365.

As to array formulas...
I have been using many of the new dynamic array formulas that 365 has to offer, but my fast version has those too. Do dynamic arrays calculate faster than traditional CSE formulas?

 

[Fluff]

Do dynamic arrays calculate faster than traditional CSE formulas?

Very much so, they are massively quicker than array formulae.

 

[sriche01]

If you're processing data, again depending on "what" it is probably faster to do this in memory vs on the sheet

Can you explain this please?

 

[JackDanIce]

Using arrays to read, process and output data instead of spreadsheet objects to manipulate