Why would a spreadsheet "magically" run faster?

[mrblister]

I'm working on a very large project. Each time I save, I save under a new filename. The spreadsheet runs pretty slow. I have a Macro that I run periodically to make sure the spreadsheet is as optimized as possible, but it takes about 1.3 seconds for any calculation.

A day ago I noticed the spreadsheet seemed to be running faster. I ran the timer macro, and it calculated the time between calculations is 0.8 seconds now. I can't figure out why.

I tried loading a slightly older build from a few days before (1.3 seconds between calcs), and I copied over all the values and VBA from the newer sheet (even though it should not have made a difference, as the data hasn't changed much and the VBA hasn't been updated recently), but the slightly older sheet still runs at 1.3 seconds.

Both sheets, with the same data seem to give the exact same calculations, with the exception that the "newer" sheet runs significantly faster. I can't figure out why! It worries me, because I'm worried I "broke" functionality somewhere and my test data simply hasn't revealed the issue... yet. Any ideas? Did Microsoft update Excel recently that may have triggered this change?

And no, the spreadsheets are huge (and for a client) so I can't upload them. Just asking for ideas right now.

 

[mole999]

any of the CF ranges broken, i.e. it steps up and down in number groups due to deletions and additions, replace any that aren't 100% consistent, also saving / shutting down and restarting will make some subtle differences

 

[mrblister]

any of the CF ranges broken, i.e. it steps up and down in number groups due to deletions and additions, replace any that aren't 100% consistent

Hi Mr. Mole. I'm afraid I don't know what you mean. Could you give me more details please?

 

[mole999]

in the CF rules they normally look like

=$F$8:$F$18

but they can get like

=$F$8:$F$10,$F$12:$F$18 etcetera, applying broken rules when in appropriate would mean that it has to be applied as given, rather than intended

 

[mrblister]

I deleted all the conditional formatting (all sheets) in the slow sheet. There is no change in calculation speed.

 

[PaddyD]

If you really want to get into this, I'd suggest:

Why FastExcel V3 - Decision Models

...if you didn't know about it already

 

[mrblister]

If you really want to get into this, I'd suggest:

Why FastExcel V3 - Decision Models

...if you didn't know about it already

No, I didn't know about it. Seems a little pricey, but I may not have a choice. My "speedy" spreadsheet has slowed down again, now moving as the same speed as the older spreadsheet. Not sure what caused it. I was working on some VBA modules, and I added new columns. But the speed drop was immediate, it went from 0.8s per calculation back to the old speed of 1.3s per calculation. I'm going to try to copy-over my changed values from the "newest" workbook to the "speedy" workbook, to try to keep the faster speed and the newest values/changes.

 

[mole999]

its free to try, maybe you can identify the issues in a short period of time