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.

 

[JLGWhiz]

The only explanation I can think of, and it is based purely on assumption, is that when you do the SaveAs to a new file name, it only writes the pertinent data and code of the original file and does not write the garbage along with it. If you check the file size, you will probably see that the newer file is slightly less in KB than the original. The trailing variables, deletions, etc. that were stored in the oringinal were most likely ignored to create the new file.

 

[Logit]

Saving a workbook as ".xlsb" can make a difference.

Otherwise, perhaps something was corrupted in the previous version but isn't now ?
Or the amount of data has decreased ?

 

[mrblister]

Saving a workbook as ".xlsb" can make a difference.

Otherwise, perhaps something was corrupted in the previous version but isn't now ?
Or the amount of data has decreased ?

The file sizes between the slow file and the fast file are the same (actually, the fast file is ~2 mb larger).

On a hunch, I tried saving the slow file under a new name (maybe Excel got an update that sped up some calculations). But the new slow file is still slow.

I saved as XLSB, and the new file size is less than half the size! But I can't run the spreadsheet. It takes a while to load, and then it's constantly "calculating" (stuck at 0% calculation).

Going to try a few more things.

 

[FDibbins]

Do a quick check in both files (on all sheets) to see where the last row/column is, and see if they compare.

A quick END-HOME will take you to the last "used" bottom-right cell. If that is not where your last row and column of data really is, start CAREFULLY!!!!!!!! deleting rows and columns until you get to where you KNOW FOR SURE your data ends. Save under a new name, close, re-open and check again for speed

 

[Logit]

FD:

That method has helped me a number of times here.

I still don't understand how Excel can 'grab' all those additional rows when nothing in the code tells it to.

 

[FDibbins]

I think it's a "hold-over" from where you used to have data or formatting. Excel "remembers" where it thinks your data range ended

 

[mrblister]

Do a quick check in both files (on all sheets) to see where the last row/column is, and see if they compare.

A quick END-HOME will take you to the last "used" bottom-right cell. If that is not where your last row and column of data really is, start CAREFULLY!!!!!!!! deleting rows and columns until you get to where you KNOW FOR SURE your data ends. Save under a new name, close, re-open and check again for speed

Thanks! I gave it a try, but it seems the end-cell looks the same on all tabs, on both spreadsheets.

I just did a more extensive data test. Both sheets give the same calculations, with the exception that the "faster" sheet calculates... faster. OK, well, I'm not one to look a gift horse in the mouth. The new faster workbook runs faster and gives the same results (even though I have no idea why), so I'm going to keep using it.

 

[FDibbins]

What are some of the formulas you are using?
Any array formulas referencing full-column ranges (avoid)
Any Conditional Formatting over large ranges? (avoid)
Any Data Validation over large ranges? (avoid)

 

[mrblister]

What are some of the formulas you are using?
Any array formulas referencing full-column ranges (avoid)
Any Conditional Formatting over large ranges? (avoid)
Any Data Validation over large ranges? (avoid)

Hi FD,

Formulas haven't changed much (I don't think any formulas have changed at all), and I always try to avoid referencing the entire column. I've just been manipulating data.
Conditional formatting hasn't changed.
There is no data validation in this workbook.