File (7MB) takes 25s to save each time (?)

[d0rian]

I don't remember just when this happened (otherwise i could probably troubleshoot), but my file now takes 20-25s to fully complete the Save process every time I Ctrl-S save. It's a large-ish file (7-MB) with a lot of formulas spread out over a dozen sheets, but I don't remember it ever being this bad. And I have files twice as big that save in under 3 seconds (15-MB, albeit with nowhere near as many formulas)

1) What's best way to troubleshoot this?
2) Are there certain processes that are known to slow down the saving process that I should look to remove? (I thought maybe all the calculations were the culprit, but I turned off Auto-recalc and the save takes just as long...)

 

[RoryA]

If you set calculations to manual, the default is to recalculate before saving. The fact that the times are the same either way makes me think your workbook is recalculating when you save it - do you have volatile formulas in it like OFFSET, NOW, or INDIRECT? Iterative calculations turned on?

 

[d0rian]

No volatile formulas (a few months ago, I made a big effort to purge all such calcs after learning how badly they slow down files) But now I'm really perplexed because:

- I systematically deleted each worksheet one by one and then re-saved the file, hoping to ID the culprit; the save time improved by a couple seconds, but is still painfully slow.
- I even got down to the point where I deleted every single sheet (well, I had to create a new one, 'Sheet2', because it wouldn't let me delete the last sheet in the book)
- Now I literally have an Excel file with a single BLANK worksheet (11kb in size) that takes 10+ seconds to either open or save.

This...makes no sense to me. FWIW I deleted all Macros, and all Forms and modules in the VBA editor. There are no conditional formatting rules left (b/c the only sheet is the fresh Sheet2 I created). This is, by any measure, a pretty much empty file. Why could it be taking 10+ seconds to open or save? Some kind of latent corruption?

 

[RoryA]

Do you have any defined names in the workbook?

 

[d0rian]

Sorry, meant to write in previous post that I'd also deleted all Defined Names. (Yes, I had several hundred)

 

[RoryA]

Are you saving the workbook locally, or to a network location?

 

[d0rian]

Locally. It is a file that I created about 18 months ago and has been through many iterations, with a ton of formulas, conditional formatting, API connections to servers (it pulled in real-time stock market data)...probably some crashes along the way, i.e. plenty of things that I suppose conceivably could have corrupted something (although I did my best to back it up daily, and revert to one of those backups any time there was a crash or anything weird.)

That being said, it still doesn't explain why, even after I've stripped out everything that I can possibly think of, it would take 10+ seconds to either open or save. Even had it gotten corrupted along the way, where could any latent data / corruption be hiding in an apparently empty 11-kb file that would be causing this?

 

[RoryA]

I'm not sure. Can you put it on a file hosting site for me to take a look at?

 

[Fluff]

What do you get if you run this

Sub chk()
MsgBox ActiveWorkbook.Names.Count
End Sub

 

[d0rian]

What do you get if you run this

Sub chk()
MsgBox ActiveWorkbook.Names.Count
End Sub

A popup box with the number zero in it (which I can close by pressing the OK button on it.)

(Both you and RoryA seemed to ask about the Named Ranges issue...is that something that conceivably might be contributing to this? -- notwithstanding the fact that there don't appear to be any Named Ranges in the doc anymore...)

(As far as uploading it, the file at one point had some sensitive info in it and...I'd be a little skittish uploading it anywhere, even if that means diagnosing this is harder; thanks for the offer though Rory.)