0

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?

Community
  • 1
  • 1
Pauli Price
  • 4,187
  • 3
  • 34
  • 62
  • Can you post the code that you are using to quit the app? If you are quitting through VBA, you should be able to ensure that excel terminates properly. Without your code it is difficult for me to guess what the issue might be. – Pynner Nov 22 '12 at 00:58
  • Maybe there's a remote reference to your Excel's Application object, or a workbook, or a spreadsheet, etc. – acelent Nov 22 '12 at 22:50
  • I'm not quitting the app in my code. When the user manually exits excel by closing the last open workbook, or using exit via the menu, Excel appears to quit, but sometimes leaves a zombie instance running. In the event, when the user next opens excel, there will be two instances running, each consuming ~50% of CPU. The zombie excel shows up on the process list, but not on the applications list, of the windows task manager, and remains until it is forced to exit. – Pauli Price Nov 23 '12 at 23:52

1 Answers1

0

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.

Community
  • 1
  • 1
Pauli Price
  • 4,187
  • 3
  • 34
  • 62