Excel VBA project not getting closed

Question

I have a COM addin in which I am handling excel events. I click Alt F11 to view the VBA project related to an excel. However, when I close the workbook the Excel VBA project in the project explorer is not closed as well.

It gets closed normally when my COM addin is not installed. What is going on, and what should I do differently?

Answer 1

The problem was with workbook objects remaining in the memory. By clearing the objects in the workbook deactivate event, the VBA project is getting closed when I close the workbook.

See this: http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/377fbebc-021d-4e48-aaef-d4658cc221b3

Thanks

Answer 2

I had a similar problem in my COM-Add-In (C# / VSTO):

• You have to release all Workbook references in your code when the workbook is closed and set them to null.
• If you internally store a reference to a workbook, always use WeakReference to let the Garbage Collector do its work.

In my case I had a Windows.Forms.Tree of all workbooks in a custom task pane. The referenced Workbook objects were stored in the TreeNode.Tag. After I had closed a workbook, the tree was updated by events, but at this time I had already generated what I've called a VBA Workbook "ghost". I solved this by replacing the Workbook-Objects in the TreeNode tags by the Workbook.FullName-String (to reference them later).

Hope it helps, Jörg