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

d0rian

Active Member
Joined
May 30, 2015
Messages
251
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...)
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,485
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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:

d0rian

Active Member
Joined
May 30, 2015
Messages
251
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,485
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Do you have any defined names in the workbook?
 

d0rian

Active Member
Joined
May 30, 2015
Messages
251

ADVERTISEMENT

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

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,485
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Are you saving the workbook locally, or to a network location?
 

d0rian

Active Member
Joined
May 30, 2015
Messages
251

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,485
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I'm not sure. Can you put it on a file hosting site for me to take a look at?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,010
Office Version
  1. 365
Platform
  1. Windows
What do you get if you run this
Code:
Sub chk()
MsgBox ActiveWorkbook.Names.Count
End Sub
 

d0rian

Active Member
Joined
May 30, 2015
Messages
251
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.)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,527
Messages
5,523,391
Members
409,515
Latest member
chemitek

This Week's Hot Topics

Top