Sheet events (cell change,selection change) not firing after running background thread in c# addin for excel

Question

I'm developing a VSTO add-in for excel. I've used a background worker thread to execute some piece of code in the background so that user is free to work on the excel sheet meanwhile.

I registered listeners for cell change and selection change events using the sheet_activate event handler in the main code as shown in the code :`

    private void thisWorkbook_SheetActivate(Object sheet1)
    {
        try
        {
            if (sheet1 is Worksheet)
            {
               Worksheet sheet = sheet1 as Worksheet;
               sheet.SelectionChange += eventDel_SelectionChange;
               sheet.Change += eventDel_CellsChange;
            }
        }
        catch (Exception e)
        {
            printException(e);
        }
    }` 

Before running the background worker thread, these events are getting triggered as expected. However, after running the background worker thread,somehow these events are not getting triggered.

I have to execute thisWorkbook_SheetActivate again to solve this issue. I have to do this every time after running the background worker thread. The code piece in my background worker analyses the data already present in the worksheet, makes some changes and then updates the data in the sheet.

I do realise that excel uses COM (Component Object Model) and STA(Single Threaded Apartment) to execute threads i.e. only a single thread can execute in the apartment at a time. I'm not sure if this might be causing the problem.

Could someone explain why is this happening? What are the possible solutions to solve the issue?

I'm currently working on Microsoft Visual Studio 2010 and Microsoft Excel 2007.

Thanks in advance!

Answer 1

If you are accessing the excel methods like ranges or updating cells on the background thread, then you will have an issue. You are lucky so far that the code did not crash and/or throw an exception. I am pretty sure that it is throwing an exception and you may not be catching it. As for the solution, what you need to do is to get all the data from the sheet you want in the event handlers and then use that "data" in your background thread, and don't access excel UI from the bgnd thread. When the thread finishes its work, you need to some how invoke a method on the main UI thread (there are plenty of examples on how to update main UI thread controls using Invoke delegate).

Answer 2

The sender of the event (the workbook in your case) must be stored in a field and not in a local variable to not be disposed. When the object is disposed, the event is gone.