Slow to close workbook if another workbook open

[Sinon]

Hi all! Not really sure what supporting info to provide but I have a spreadsheet that has a rather odd behaviour.

The workbook has a lot of formulae and a sheet that has over 7000 rows. That sheet has about 10 columns of formulae which include INDEX/MATCHES referring to a couple of tables in other sheets. There's also data validation with 3-5 values. There are no macros in the workbook but it's saved as an xlsb. It does not refer to any other workbooks.

Everything works fine and it's not too slow when amending various cells. The problem arises when closing the file. It takes a good 2-3 minutes for the file to close, regardless of whether it saves or not. However, this only happens if there is another workbook open. I tested various scenarios and, as long as this workbook is the only one that is open, it closes in seconds. As soon as I open another workbook, even a blank one, it takes 2-3 minutes. I tried it both on our shared server and moving it to my own machine and the behaviour is consistent. The workbook is meant to be used with at least another workbook open so this is an issue as, depending on the user's machine, it may even crash and close all Excel files.

I think the formulae are not the issue as closing the workbook works as expected if it's the only excel workbook open. For the life of me, I don't know what is causing this issue. My last resort is to start fresh and redo the workbook as the workbook has been recently modified but in place for a couple of years. However, that would be an extensive job and take most of a day and I'm reticent to do this.

Any suggestions would be greatly appreciated, or if anyone has encountered this before.

Thanks!!

 

[GPerky]

Hi All,

I see this forum is old & had no replies.

I have had this frustrating issue & spent ages trying to diagnose what has been causing it & searching through forums.

We are on version Microsoft 365 64 Bit.

My model is 32MB with a lot of SUMIF & INDEX/MATCH formulas. It takes up to 1 1/2 minutes to close with another blank workbook open, but closes instantly if no other workbooks are open.

I have tried the following:

- Delete sheet by sheet to try & diagnose the cause
- removed all customized styles
- removed all conditional formatting
- de-activated all Add-Ins

The model closes slightly faster everytime I delete a sheet, so I'm assuming it has to do with the size of the model.

I would be very happy to include some VBA "On Close" which forces saving the workbook & then forces a quick close.

Any suggestions, questions or even just simply confirming this is normal behavior for Excel would be really appreciated

Thanks

Perky

 

[GPerky]

Any suggestions, questions or even just simply confirming this is normal behavior for Excel would be really appreciated!!!!

 

[offthelip]

Office 365 does seem to have some problems with "wasting" time. I suggest trying it when you are offline and not connected to the internet, that often seems to cure the office 365 time problems. It would at least give a clue as to where the problem is.