Incorrect async/await working, Excel events in Excel Application Level Add-in

Question

I faced a problem with using async /await in my add-in application, I have custom task pane and user can download some data with it, so there are problems:

1. After awaited operation completed the control is not returned to previous execution context. I created example with windows forms and paste the same method with awaiting and it works fine - I can use form controls after await operation, but not in add-in, I have to call custom task pane Invoke method. Simple example:

   private void SearchPaneButton_Click(object sender, EventArgs e)
   {
       Search();
   }
   
   private async void Search()
   {
       var searchText = SearchTextBox.Text;
       SearchPaneButton.Text = "Loading…";
       var data = await new DataServiceClient().GetDataAsync(searchText);
       this.Invoke((MethodInvoker)(() =>
       {
           SearchPaneButton.Text = "Search";
           ToggleWorkbookEvents();
       }));
   }
   

2. In my add-in I need to handle some events - worksheet changed, worksheet activated/deactivated, but these events disappear and isn't fired after await operation and I have to use something like "toggling"(remove+add) events after each await using. Simple example:

   private void ToggleWorkbookEvents()
   {
       var excel = Globals.MyAddIn.Application;
       //need to ensure event will not be set twice
       excel.SheetActivate -= CheckSheetForData;
       excel.SheetActivate += CheckSheetForData;
       if (excel.ActiveWorkbook != null)
       {
           var activeSheet = Globals.MyAddIn.GetActiveWorksheet();
           //need to ensure event will not be set twice
           activeSheet.Change -= Worksheet_Changed;
           activeSheet.Change += Worksheet_Changed;
       }
   }
   

So maybe vsto framework can not support new features(like async await) of last versions .NET Framework?

Topdress

@SpaceSteak I have added some code examples, thanks – Topdress

Why aren't you awaiting on Search? – Highkeyed

@YuvalItzchakov Because I don't need to wait the Search method in the click event handler, just invoke – Topdress

What happens if an exception occurs? – Highkeyed

@YuvalItzchakov I know I can't handle exception in the event handler in this one(but can handle in Search method for example), i just want to show that I had to use MethodInvoker and Search method is called from button click event. – Topdress

Answer 1

This is a long-standing issue with Office plugins: they don't provide a SynchronizationContext.

As I mention on my blog, you can work around this by ensuring you have a proper SynchronizationContext. This is a bit hacky, but it works:

private async void SearchPaneButton_Click(object sender, EventArgs e)
{
  if (SynchronizationContext.Current == null)
    SynchronizationContext.SetSynchronizationContext(new WindowsFormsSynchronizationContext());
  await SearchAsync();
}

private async Task SearchAsync()
{
  var searchText = SearchTextBox.Text;
  SearchPaneButton.Text = "Loading…";
  var data = await new DataServiceClient().GetDataAsync(searchText);
  SearchPaneButton.Text = "Search";
  ToggleWorkbookEvents();
}

As far as the "toggling events" problem goes, I have no idea.

Lollard

Why do you consider it a bit hacky? – Confederation

thanks, as I understand does background worker not solve my problem? I can split Search method on background worker events(instead of async/await) and use synchronous GetData request, so I will get the thread exception? – Topdress

@JeremyThompson: Because your code has to do something the framework should be doing not just here but everywhere it calls your code. :/ – Lollard

@ermac: I'm not clear on what you're asking. I see no reason to use BGW here (or anywhere, for that matter). – Lollard

@StephenCleary I want to reworked my custom task pane to use BGW instead of async/await because want to solve the second problem and test excel event working. But in RunWorkerCompleted will I get the cross-thread exception if change the some pane UI control? – Topdress

@ermac: Running code in a thread pool thread cannot possibly solve the problem of event handlers being unexpectedly cleared. – Lollard

Answer 2

When it comes to your second question - events being unbound from Worksheet, Workbook etc. you have to store the reference to the target object as a property or field of class that binds those events. For example in your ToggleWorkbookEvents method you reference Application as a local variable, you could try to store it like that:

private Microsoft.Office.Interop.Excel.Application _application = Globals.MyAddIn.Application;

I believe this happens due to target event handlers being removed during garbage collection.

I base this entry on an answer by Peter: https://mcmap.net/q/1172088/-commandbars-onupdate-stops-being-fired

Tier