1

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!

Nikhil Chandla
  • 21
  • 1
  • 5
  • Is it possible that your event target got garbage collected? When I faced with similar problem (events in Excel not firing after async call), I found solution here: https://stackoverflow.com/a/58524311/6757641 – mmokrzycki Aug 13 '20 at 15:13

2 Answers2

0

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).

dgorti
  • 1,130
  • 2
  • 12
  • 22
  • Hi Dgorti, Thanx for the input. Actually I've implemented IMessage Interface to handle the exceptions thrown. Could you help me with any links that explain how to invoke the main thread from the working background thread. For now, I'm invoking the sheetactivate function above through a button in the GUI. Is there a way to call this function from the background thread and invoke the main thread while doing so?? – Nikhil Chandla Jan 03 '17 at 06:32
  • Also, I'm not able to find any plausible explanation anywhere as to why is this issue happening. Could you help me to understand the reason for this issue? – Nikhil Chandla Jan 03 '17 at 06:35
0

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.

Maxence
  • 12,868
  • 5
  • 57
  • 69