Out of Memory error on close tries to open protected VBA

[MrRhodes2004]

Group,

We have developed an intense spreadsheet. Though, only sometimes when the spreadsheet is closed an Out of Memory error occurs. When VBA has been unlocked and opened it shows the error. However, when a user gets the same error the VBA is closed and protected but the error still tries to open VBA anyway.

This error only happens during close and I have not been able to trap the error as it seems to happen after all the code has been executed.

Before exit, I delete all variables (even though they are only sub level and should have been killed at the end of the routine). I unload all forms that might be open. Based on interweb suggestions, I now use VBA in a non-maximized screen and "clear all breakpoints" before saving. I also have also just added "On Error Resume Next" to the end of workbook close and pre-close subs.

But I sometimes still get the error. Since the spreadsheet is being used in many countries by different users, I do not know what types of machine is being used.

Any suggestions on how to either prevent the out of memory error from showing at all or how to purge the cache or something?

Thanks...will try anything (well, almost)!

[Dave]

Trial turning off the auto document recovery "feature". HTH. Dave

[MrRhodes2004]

Trial turning off the auto document recovery "feature". HTH. Dave
Thank you Dave, I will add this to the closing sequence.

[MrRhodes2004]

Thank you Dave, I will add this to the closing sequence.
I added Application.AutoRecover.Enabled = False to the closing sequence while everything processes and then set it to True as the final command of the Workbook_close event.
When I saved the file... the random Out of Memory showed its little head. I think it did it out of spite to show that the option did not work.
Thanks Dave for the suggestion.
Any other suggestions?

[SamT]

Any other suggestions?A close analysis of the Program Flow thru all the code?

Put this in a handy place.

Sub Testing_TriesToOpen()
Stop
End Sub.
Save the book.


Run Testing_, then close the book.
Repeat several times or until that ugly little head pops up again.

Note that you should NOT run that sub from any other procedure..

[SamT]

You might see what https://www.mztools.com/v8/download_trial.aspx can do? I've never used it, so . . .

[Dave]

When I saved the file... the random Out of Memory showed its little head. I think it did it out of spite to show that the option did not work.
No. It was a clue that in fact the auto recover was messing with U. Turn it off before close and leave it off. If U want it on, turn it on during workbook open. HTH. Dave

[MrRhodes2004]

Dave, I will try that. Thanks again for the input.