Excel Workbook.SheetDeactivated events disappearing

[Steve Kirk (Insomniac Games)]

Hi
I'm working on an excel add-in using the 2003 PIAs and VS C# .Net 2003

If I attach a Workbook.SheetDeactivate event it fails to get called
after the about the 40th call if I do this


private void SheetDeactivate(object _sheet)
{
try
{
Excel.Worksheet sheet = (Excel.Worksheet) _sheet;

for (int i = 0; i<50; i++)
{
Excel.Range cur_cell = (Excel.Range)sheet.Cells[1,1];
}

}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.ToString(),"Addin");
}
}


further, if I try to reattach the event (using a button click event on
my custom button)

It gives an exception on detach and attach


Detaching the SheetDeactivate Event

System.NullReferenceException: Object reference not set to an instance
of an object.

at System.Runtime.InteropServices.UOCMIConnectionPoint.Unadvise(Int32
dwCookie)
at
Microsoft.Office.Interop.Excel.WorkbookEvents_EventProvider.remove_SheetDeactivate(WorkbookEvents_SheetDeactivateHandler)
at
Microsoft.Office.Interop.Excel.WorkbookClass.remove_SheetDeactivate(WorkbookEvents_SheetDeactivateHandler)
at Addin.Connect.Button_Click(CommandBarButton cmdBarButton, Boolean&
cancel)



Attaching the SheetDeactivate Event

at System.Runtime.InteropServices.UOCMIConnectionPoint.Advise(object
pUnkSink, Int32& dwCookie)
at
Microsoft.Office.Interop.Excel.WorkbookEvents_EventProvider.add_SheetDeactivate(WorkbookEvents_SheetDeactivateHandler)
at
Microsoft.Office.Interop.Excel.WorkbookClass.add_SheetDeactivate(WorkbookEvents_SheetDeactivateHandler)
at Addin.Connect.Button_Click(CommandBarButton cmdBarButton, Boolean&
cancel)



Any ideas what I could be doing wrong?

Thanks

 

[Peter Huang [MSFT]]

Hi,

Based on test, I can not reproduce the problem,

After the for loop,the event will keep firing,here is my test code for your
reference.
Also you may try to take a look at the link below to ReleaseComObject
explictly.
317109 Office application does not quit after automation from Visual Studio
http://support.microsoft.com/?id=317109

private Excel.Application exApp=null;
private Excel.Workbook oWkBook=null;
public void OnConnection(object application, Extensibility.ext_ConnectMode
connectMode, object addInInst, ref System.Array custom)
{
exApp = application as Excel.Application;
oWkBook = exApp.ActiveWorkbook;
oWkBook.SheetDeactivate +=new
Microsoft.Office.Interop.Excel.WorkbookEvents_SheetDeactivateEventHandler(oW
kBook_SheetDeactivate);
}
private void oWkBook_SheetDeactivate(object Sh)
{
Excel.Worksheet oSheet = Sh as Excel.Worksheet;
Debug.WriteLine(oSheet.Name);
try
{
Excel.Worksheet sheet = (Excel.Worksheet) Sh;

for (int i = 0; i<50; i++)
{
Excel.Range cur_cell = (Excel.Range)sheet.Cells[1,1];
}
Debug.WriteLine("Exit For");
}
catch (Exception ex)
{
Debug.WriteLine(ex.ToString(),"Addin");
}
}

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Gavin]

That works fine, but if you change OnConnection to this

connectMode, object addInInst, ref System.Array custom)
{
exApp = application as Excel.Application;
//added Excel.Workbook
Excel.Workbook oWkBook = exApp.ActiveWorkbook;
oWkBook.SheetDeactivate +=new
Microsoft.Office.Interop.Excel.WorkbookEvents_SheetDeactivateEventHandler(oWkBook_SheetDeactivate);
}


the call back will fail after about 10 callbacks.

Do I have to keep every object I want to add callbacks to?

 

[Peter Huang [MSFT]]

Hi

As we know .NET has a GC thread to do the object dispose, and when and how
GC do the job is controled by CLR.
So in this scenario, the GC may not wise enough to release the cur_cell COM
reference in the right Order, although due to C# syntax, after the } in the
for, the cur_cell will be disposed, but since it is in the .NET world, the
GC do the acutally dispose work, it will not running all the time, the CLR
implement the algorithm about how GC does

So one way is that you may try to release it manually.
for (int i = 0; i<50; i++)
{
Excel.Range cur_cell = (Excel.Range)sheet.Cells[1,1];
Marshal.ReleaseComObject(cur_cell);
cur_cell = null;
}

Or you would betterprivate Excel.Workbook oWkBook=null; declare it as class
member, or we have no change to call the ReleaseComObject, because it is
local var.
But if we ReleaseComObject it immediately in the OnConnection, then the
event will not fire, because the object has been released.

So I suggest you following the KB article I provide before to design your
application.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Gavin]

Hi,
I understand what is happening now, though it isn't exactly intuitive.
Thanks for the help.

 

[Peter Huang [MSFT]]

Hi

You are welcomed!
If you still have any concern, please feel free to post here.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.