Excel Workbook overloading with memory when closing-CodePudding

I have a workbook that is about 8mb with about 20 different API calls (via formula in cells) using a 3rd party service. Everytime I try to close the workbook, excel goes Not Responding and the app comes to halt. The memory shoots up to like 2000-3000MB and sometimes it closes and other times I get a memory crash.

I do have a lot of vba code but nothing is running when I try to exit without saving. As far I know the object memory goes null after a subroutine runs so I dont know where the memory leak is coming from.

Any ideas?

CodePudding user response：

I would start by turning off CalculateBeforeSave when you close the workbook.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.CalculateBeforeSave = False
End Sub

CodePudding user response：

For whatever reason, if I open a blank workbook first and then open the big file ( FileX.xlsm) mentioned above when I close FileX, it has no memory leak. But if I open FileX first and then try to close the memory shoots up to 3000-4000mb and my computer crashes.

Im wondering if the 3rd party add-in doesnt configure properly when FileX is opened from the start. Maybe by opening a blank file first the 3rd party add-in configures properly and prevents memory leaks when closing other big files.