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

d0rian

Active Member
Joined
May 30, 2015
Messages
313
Office Version
  1. 365
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...)
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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?
 
Last edited:
Upvote 0
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?
 
Upvote 0
Do you have any defined names in the workbook?
 
Upvote 0
Sorry, meant to write in previous post that I'd also deleted all Defined Names. (Yes, I had several hundred)
 
Upvote 0
Are you saving the workbook locally, or to a network location?
 
Upvote 0
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?
 
Upvote 0
I'm not sure. Can you put it on a file hosting site for me to take a look at?
 
Upvote 0
What do you get if you run this
Code:
Sub chk()
MsgBox ActiveWorkbook.Names.Count
End Sub
 
Upvote 0
What do you get if you run this
Code:
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.)
 
Upvote 0

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top