I'm developing an app that uses Excel on the front end and accesses remote data through a 3rd party API over COM. The app is directly coded in Excel VBA (i.e. no COM automation of Excel.) Sometimes Excel doesn't exit when the user quits, after running my app, thereby creating a zombie Excel that consumes ~50% of CPU.
I've read the existing 'Excel won't exit' answers here on SO - they all seem to be related to Interop / COM automation of Excel. Any suggestions on how to ensure Excel exits in this case?
I've come to believe that COM objects created by my code are not being disposed of properly, preventing Excel from exiting completely. I attempted to force their release, leading to this question: How can I call System.Runtime.InteropServices.Marshal.ReleaseComObject from within an Excel 2007 VBA module -- quoting from one answer (also includes related links worth checking out):
Are you sure you are dropping all references to the COM object when you want Excel to exit? Make sure that you are by placing lines like the following for every reference you hold to the COM object:
obj = Nothing ' Where "obj" is a reference to the COM object
If that doesn't solve it, it's also possible that the problem is a circular reference. Does the COM object store a refenrece to a VBA object of yours which in turn holds a reference to the COM object? If so, a circular reference will be created and the objects will never be released.
I now suspect circular references. If the problem goes away once I set certain COM object properties to Nothing before setting the objects themselves to Nothing in the workbook close event, I'll come back and mark this as the accepted answer.
Update
Careful setting of object references to Nothing didn't resolve the problem. I ultimately created a COM visible dll wrapper for System.Runtime.InteropServices.Marshal.FinalReleaseComObject. Calling dispose on the COM objects in the Workbook BeforeClose event appears to have resolved the issue.
