Sunday, December 18, 2005

Garbage Collection of CommandBarButtons

I am building an Excel Addin in C#. It starts with creation of a single
commandbutton on the Excel toolbar (call it B1). When clicked this opens
a form with a few Pictureboxes. These pictureboxes can either be clicked
to start some routine or dragged onto the Excel toolbar to become a
commandbutton (call them B2). The commandbutton B2 has a clickhandler
event and has OnAction as . When I click the
pictureboxes on the Form, the form is made invisible. The pictureboxes
on the form fire ok, but after a series of events, the commandbutton
click events do not fire anymore.

e.g
1. open Excel, click a commandbutton previously dragged onto the Excel
toolbar (B2) - fires ok
2. click commandbutton on toolbar (B1) and click a pictureBox on Form -
fires ok
3. click (B2) - does not fire
4. click (B1) and click a pictureBox - fires ok

I should note that each time the commandbutton or picbox is clicked the
excel object is obtained from your method given in
http://blogs.officezealot.com/whitechapel/archive/2005/04/10.aspx

Andrew`Whitechapel: Second, the behavior you describe for the event handlers sounds exactly
like the classic delegate scoping issue. The object that holds the event
sink (delegate) must be declared at class scope, not at function scope.
If you declare it at function scope it will go out of scope at the end
of the function and become available for garbage collection. It will
then actually be garbage collected at some indeterminate point in the
future.

So, my advice is to use the Excel.Application object given to you up
front, and cache this at class scope. Then also cache the
CommandBarButton object at class scope.

Labels: , ,

Creating Excel Addin with C# Extensibility

Well it has been a long time since my last.
I have been using C# over the last few months to automate an Excel Addin and have run into every gotcha known to man!
I will be compiling my experiences and hopefully they will be helpful to new Excel/Office Addin programmers.

Labels: ,