none
AddHandler doesn't work the first time it's called! Very weird RRS feed

  • Question

  • We are working on an Excel Addin. It was originally targeted to .Net 3.5 and Office 2007. Now, we have converted it to .Net 4. Some things now work better but new framework-related bugs appeared:

    Some features stopped working and after a few tests I found out that the AddHandler instruction doesn't work at its first call.

    I have to do things like

    AddHandler worksheetItem.Worksheet.Change, AddressOf Globals.Ribbons.IGRibbon.worksheetChangesHandler
    
    RemoveHandler worksheetItem.Worksheet.Change, AddressOf Globals.Ribbons.IGRibbon.worksheetChangesHandler
    
    AddHandler worksheetItem.Worksheet.Change, AddressOf Globals.Ribbons.IGRibbon.worksheetChangesHandler

    to actually get worksheetChangesHandler to handle that event. That never happened before (with .Net 3.5 and Office 2007).

    Do you know what this bug is related to and if there is some workaround different than adding the handler twice?


    • Edited by FerchoArg Sunday, June 17, 2012 2:03 AM grammar
    Sunday, June 17, 2012 2:02 AM

Answers

  • when you attach to events in .net in general you have to make sure that object that exposes that event is always available to you (does not get garbage collected). So in your case when you attach to workbook and worksheet events you have to store them somewhere (for example your add-in class instance).
    • Marked as answer by FerchoArg Saturday, June 23, 2012 3:34 AM
    Sunday, June 17, 2012 5:33 AM

All replies

  • More specific details:

    These AddHandlers DO work:

    Private Sub IGAddIn_Startup(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Startup
    
    ' [...]
    
       AddHandler Application.WorkbookActivate, AddressOf ThisWorkbook_Activate
       AddHandler Application.WindowActivate, AddressOf Application_WindowActivate
       AddHandler Application.WindowDeactivate, AddressOf Application_WindowDeactivate
       AddHandler Application.WorkbookOpen, AddressOf Application_WorkbookOpen
    
    ' [...]
    
    End If

    These ones need the workaround:

    Private Sub Application_WorkbookOpen(ByVal workbook As Microsoft.Office.Interop.Excel.Workbook)
    ' [...]
       AddHandler workbook.SheetFollowHyperlink, AddressOf FollowHyperlinkEventHandler
       RemoveHandler workbook.SheetFollowHyperlink, AddressOf FollowHyperlinkEventHandler
       AddHandler workbook.SheetFollowHyperlink, AddressOf FollowHyperlinkEventHandler
    ' [...]
       AddHandler worksheetItem.Worksheet.Change, AddressOf Globals.Ribbons.IGRibbon.worksheetChangesHandler
       RemoveHandler worksheetItem.Worksheet.Change, AddressOf Globals.Ribbons.IGRibbon.worksheetChangesHandler
       AddHandler worksheetItem.Worksheet.Change, AddressOf Globals.Ribbons.IGRibbon.worksheetChangesHandler
    ' [...]
    
    End Sub

    ' [...] represents more code, but I also made tests JUST with AddHandler to see if other code was conflicting but the problem still occurred.


    • Edited by FerchoArg Sunday, June 17, 2012 2:15 AM
    Sunday, June 17, 2012 2:14 AM
  • when you attach to events in .net in general you have to make sure that object that exposes that event is always available to you (does not get garbage collected). So in your case when you attach to workbook and worksheet events you have to store them somewhere (for example your add-in class instance).
    • Marked as answer by FerchoArg Saturday, June 23, 2012 3:34 AM
    Sunday, June 17, 2012 5:33 AM
  • I understand. But shouldn't the 'workbook' object be safe from being collected by the GC at this point? It is the Excel workbook that is being opened (the argument of the Application_WorkbookOpen handler. Maybe it is a kind of wrapper that is not the real 'workbook' object Excel is working with after opening the file.
    Sunday, June 17, 2012 9:36 PM
  • no, workbook reference in .net is just a wrapper, created for the need of event handler, the same with worksheet, if you do not store them somewhere, they will go out of scope.
    Monday, June 18, 2012 8:06 AM