A possible bug with Excel DNA - VBA window shows ghost workbook even when file is closed.

[Kedar Kulkarni]

Hi All

I am having a very small project which consistently reproduces the issue on 2 machines the least on office 365. I am unsure if it's a known issue. I have created a small project which demonstrated the issue on my pc and added the link. After running the code open vba window and try creating 4 workbooks and close all of them by pressing Ctrl+F4- the vba still shows the workbooks in vba project explorer.  The excel closes without hanging / keeping memory instance but I am unsure if there is a workaround to avoid this issue or if I am doing something fundamentally wrong. (I can open a bug if needed in github if it is reproducible to others.)

https://1drv.ms/f/s!AiChb_QuJ6lsg65cRPwsSdqN47EPtg?e=aP5EVk 

The code is very simple 

using ExcelDna.Integration;
using ExcelDna.Integration.CustomUI;
using Microsoft.Office.Interop.Excel;

namespace ClassLibrary1
{
    internal class ribbon: ExcelRibbon
    {
        private readonly Application application;
        public ribbon()
        {
            application = (Application)ExcelDnaUtil.Application;
            application.WorkbookActivate += ApplicationOnWorkbookActivate;
            application.WorkbookDeactivate += ApplicationOnWorkbookDeactivate;
            application.SheetActivate += ApplicationOnSheetActivate;
            application.SheetDeactivate += ApplicationOnSheetDeactivate;
        }

        private void ApplicationOnSheetDeactivate(object sh)  { }

        private void ApplicationOnSheetActivate(object sh) { }

        private void ApplicationOnWorkbookDeactivate(Workbook wb) { }

        private void ApplicationOnWorkbookActivate(Workbook wb)  { }
    }
}

thanks,

Kedar

[Kedar Kulkarni]

on an interesting note - opening the same file twice shows the same file being opened in the vba window twice. The excel closes fine so does not seem to cause any notable issues but I am unsure if it could break for users on other versions of excel as I don't have earlier versions of excel to test with. thanks

[Govert van Drimmelen]

Hi Kedar,

This is a case where the lazy garbage collection of .NET becomes observable.

Because you have those event handlers, there are runtime wrappers (RCWs) created for the COM workbook objects. 

These wrappers will keep the COM objects alive as long as the wrappers exist.

Since you are not keeping any reference to the wrappers, they will be garbage collected at the next GC run, freeing the COM objects and clearing the entries from the VBA list.

You can explicitly force a garbage collection by calling GC.Collect(). The only trick is to figure out when to call this.

You could add a ribbon button for this, but one option to deal with your specific example is to schedule it to run after the WorkbookDeactivate

        private void ApplicationOnWorkbookDeactivate(Workbook wb)
        {
            ExcelAsyncUtil.QueueAsMacro(() =>
            {
                GC.Collect();
            });
        }

That seemed to work for me, but would need some testing and I'd guess you're situation has other places where this issue arises.

Anyway, calling GC.Collect() this way is pretty safe, and will only cause noticeable delay in extreme cases that are not likely to appear in our Excel add-in world.

-Govert

[Kedar Kulkarni]

thanks a lot Govert for a quick response, I will try it and let know if any issues. You have been very helpful.