Excel VBA project not getting closed

3
votes

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?

2
c#excelcomaddinvba
has this just started to occur? can you tell us what your COM Add-In does (or what doesn't it do eg disposing/releasing) that would prevent the VBA Editor not to close? – Jeremy Thompson
No its happenning from the beginning. But I have just found a solution to this. This was happenning due to the workbook objects remaining in the memory. When I clean it using the Gargabe collector, it worked. Thanks – user1140366
Can you please put that as an answer, thanks – Jeremy Thompson

2 Answers

1
votes

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

1
votes

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