async Support in Ribbon Events

Terry Aney

unread,

Apr 18, 2024, 11:27:03 PM4/18/24

to Excel-DNA

Looking for advice on best practice, if even possible, to support async in Ribbon events - for example getContent.

I had something like the following that works fine when dropdown ribbon menu is clicked:

public async Task<string?> Ribbon_GetContent( IRibbonControl control )
{
    switch ( control.Id )
    {
        case "debugCalcEngines":
        {
            XNamespace ns = "http://schemas.microsoft.com/office/2009/07/customui";
            var menu =
                new XElement( ns + "menu",
                    new XElement( ns + "button",
                        new XAttribute( "id", "managementDownloadFile0" ),
                        new XAttribute( "imageMso", "CustomizeXSLTMenu" ),
                        new XAttribute( "label", "No files available" ),
                        new XAttribute( "enabled", "false" )
                    )
                );

            return menu.ToString();
        }
        default: return null;
    }
}

However, I need to make an async call to an Api to get info and I've tried QueueAsMacro and also ExcelAsyncTaskScheduler suggestion and ended up with the following, but it doesn't work.

1. The menu shows single dropdown item with no text (even though the Console.Writeline statements seem to run 5 secs apart so I'd expect the 'return string' value to be working).

2. If I trigger then, when I attempt to shut down Excel, something is messed up and it doesn't shutdown clean, but instead seems to shutdown the instance/workbook I was in then launches Excel again.

public async Task<string?> Ribbon_GetContent( IRibbonControl control )
{
    switch ( control.Id )
    {
        case "debugCalcEngines":
        {
            string? content = null;
            Console.WriteLine( $"debugCalcEngines: {DateTime.Now}" );

            await ExcelAsyncTaskScheduler.Run( async () => {
                var ceName = WorkbookState.ManagementName;

                var debugFiles = await GetDebugCalcEnginesAsync( ceName );

                XNamespace ns = "http://schemas.microsoft.com/office/2009/07/customui";
                var menu =
                    new XElement( ns + "menu",
                        debugFiles.Any()
                            ? debugFiles.Select( ( f, i ) =>
                                new XElement( ns + "button",
                                    new XAttribute( "id", "managementDownloadFile" + i ),
                                    new XAttribute( "keytip", i + 1 ),
                                    new XAttribute( "imageMso", "CustomizeXSLTMenu" ),
                                    new XAttribute( "onAction", "Ribbon_OnAction" ),
                                    new XAttribute( "tag", $"DownloadDebugFile|{f.VersionKey}" ),
                                    new XAttribute( "label", $"f.AuthId at {( f.DateUploaded.Date == DateTime.Today.Date ? f.DateUploaded.ToShortTimeString() : f.DateUploaded.ToString() )}" )
                                )
                            )
                            : new[] {
                                    new XElement( ns + "button",
                                        new XAttribute( "id", "managementDownloadFile0" ),
                                        new XAttribute( "imageMso", "CustomizeXSLTMenu" ),
                                        new XAttribute( "label", "No files available" ),
                                        new XAttribute( "enabled", "false" )
                                    )
                            }
                        );

                content = menu.ToString();
                await Task.Delay( 5000 );
            } );
            Console.WriteLine( $"debugCalcEngines DONE: {DateTime.Now}" );
            return content;
        }
        default: return null;
    }
}

Any advice is appreciated.

Terry Aney

unread,

Apr 18, 2024, 11:37:14 PM4/18/24

to Excel-DNA

First sample was me modifying on the fly trying to remember original version, forgot to change f() signature to public string? Ribbon_GetContent( IRibbonControl control ). Just so you don't get tripped up by that bad code in this post.

Terry Aney

unread,

Apr 18, 2024, 11:57:24 PM4/18/24

to Excel-DNA

I guess to be complete, I need async/await support in following scenarios...

1. Excel Application_* events - need to make web api calls, have some Windows forms that show that use some .NET core async methods (Stream.CopyToAsync, cryptography async methods, etc.)

2. Ribbon events - Ribbon_GetContent - need to make a web api call.
3. Actual events that happen when clicking ribbon button. Currently already have this pattern below, but need to do web api calls, will have a Windows forms that launches a background task that can be launched and immediately release control back to Excel UI while it processes

public void Ribbon_OnAction( IRibbonControl control )
{
    var actionParts = control.Tag.Split( ':' );

    ExcelAsyncUtil.QueueAsMacro( () =>
    {
        try
        {
            var mi = typeof( Ribbon ).GetMethod( actionParts[ 0 ] )!;
            mi.Invoke( this, new object[] { control }.Concat( actionParts.Skip( 1 ) ).ToArray() );
        }
        catch ( Exception ex )
        {
            LogError( $"Ribbon_OnAction {control.Tag}", ex );
        }
    } );
}

Thanks in advance.

Govert van Drimmelen

unread,

Apr 19, 2024, 4:20:40 PM4/19/24

to Excel-DNA

Hi Terry,

Suppose you have a label control and a button that will be pressed to update the label:

Then you can implement an async update of the label like this:

string _labelText = "Initial Label Text";
public string GetLabel(IRibbonControl control)
{
return _labelText;
}

public void OnUpdateLabel(IRibbonControl control)
{
Task.Run(() => FetchLabelTextAsync(control));
}

async Task FetchLabelTextAsync(IRibbonControl control)
{
await Task.Delay(5000);
_labelText = DateTime.Now.ToString("HH:mm:ss");
ExcelAsyncUtil.QueueAsMacro(() => _ribbon.InvalidateControl("label"));
}

What is important here is that I am calling InvalidateControl (from a QueueAsMacro context) in order to refresh the label after the async work is done.

This causes the 'GetLabel' to be called again, returning the updated value.

One good rule to follow (and add Debug.Assert checks if you have any doubt) is that all COM object model access must be done from the main Excel thread.

If you break this rule, Excel will not close happily.

So you really should try to split code into the Excel bits and the non-Excel calls, and not mix these.

Then you switch between these carefully, either by starting an async task or calling ExcelAsyncUtil.QueueAsMacro to switch back to an Excel context.

-Govert

Terry Aney

unread,

Apr 19, 2024, 6:27:00 PM4/19/24

to Excel-DNA

I'll do some investigation. Two questions.

1. Is Task.Run(()=>MethodAsync()) preferred over MethodAsync().GetAwaiter().GetResult()?

2. "and add Debug.Assert checks if you have any doubt" ... is there some sort of easy pattern to implement to do this? Not really sure what/where I'm asserting.

Thanks.

Terry Aney

unread,

Apr 19, 2024, 6:34:12 PM4/19/24

to Excel-DNA

Also, I can track your example above pretty easily and it makes sense. What about my example where I need to do an async call in getContent delegate of a dropdown menu? The function I assign there *must* return content. I can spawn off a fire/forget task and then call invalidateControl when that method finishes. So not sure how I'd over come that. I ended up with the GetAwaiter().GetResult() strategy and it seems to work and Excel seems to close ok. Other attempts (making handler async Task<string?> and using await inside QueueAsMacro or ExcelAsyncTaskScheduler) either didn't pause and wait for menu content or Excel didn't close. This was only way that seemed to work.

public string? Ribbon_GetContent( IRibbonControl control )
{
    switch ( control.Id )
    {
        case "katDataStoreDebugCalcEnginesMenu":
        {
            var debugFiles = GetDebugCalcEnginesAsync().GetAwaiter().GetResult();

            XNamespace ns = "http://schemas.microsoft.com/office/2009/07/customui";
            var menu =
                new XElement( ns + "menu",
                    debugFiles.Any()
                        ? debugFiles.Select( ( f, i ) =>
                            new XElement( ns + "button",
                                new XAttribute( "id", "managementDownloadFile" + i ),
                                new XAttribute( "keytip", i + 1 ),
                                new XAttribute( "imageMso", "CustomizeXSLTMenu" ),
                                new XAttribute( "onAction", "Ribbon_OnAction" ),
                                new XAttribute( "tag", $"DownloadDebugFile|{f.VersionKey}" ),
                                new XAttribute( "label", $"f.AuthId at {( f.DateUploaded.Date == DateTime.Today.Date ? f.DateUploaded.ToShortTimeString() : f.DateUploaded.ToString() )}" )
                            )
                        )
                        : new[] {
                                new XElement( ns + "button",
                                    new XAttribute( "id", "managementDownloadFile0" ),
                                    new XAttribute( "imageMso", "CustomizeXSLTMenu" ),
                                    new XAttribute( "label", "No files available" ),
                                    new XAttribute( "enabled", "false" )
                                )
                        }
                    );

Terry Aney

unread,

Apr 21, 2024, 2:46:19 PM4/21/24

to Excel-DNA

Shoot. Something definitely wrong with the above. Hitting random deadlocking issues that I need to try to understand/solve.

Terry Aney

unread,

Apr 21, 2024, 4:16:59 PM4/21/24

to Excel-DNA

I guess it boils down to `ExcelDna.Logging.LogDisplay.Show();` or `ExcelDna.Logging.LogDisplay.WriteLine();` If I call either of those before triggering my GetContent method that has `var debugFiles = GetDebugCalcEnginesAsync().GetAwaiter().GetResult();` I end up deadlocked on this line. I found this SO question that you answered that kind of hinted towards the same type of problems, but I could figure out an answer. I tried decorating my async methods inside GetDebugCalcEnginesAsync with `.ConfigureAwait( false )` but that didn't help either.

Ultimately, I ended up using the Thread Pool Hack. Think that is ok?

var task = Task.Run( () => GetDebugCalcEnginesAsync() );
var debugFiles = task.GetAwaiter().GetResult();

Brian Satola

unread,

Apr 21, 2024, 9:21:53 PM4/21/24

to Excel-DNA

I'm guessing here... Ribbon controls are designed to operate synchronously. This means that when an event like 'getContent' is triggered, it expects a response immediately. This means that you must provide a synchronous entry point that delgates the work to an asynchronous method. I've done soemthing similar for WPF forms that require async initialization, which has worked good for me:

<group id='groupTemplates' label='Templates'>
<button id='buttonTemplatesManagement'
label='Templates'
imageMso='AdpDiagramAddTable'
onAction='LaunchTemplatesManager'
size='large'
supertip='Browse and select worksheet templates.'/>
</group>

public void LaunchTemplatesManager(
IRibbonControl control)
{
ExecuteLaunchAsync(ViewModelChoice.TemplateManager,
title: "Templates Manager");
}

private async void ExecuteLaunchAsync(
ViewModelChoice choice,
string title)
{
try
{
await LaunchAsync(choice, title);
}
catch (Exception ex)
{
MessageBox.Show($"An error occurred: {ex.Message}");
throw;
}
}

I'm guessing something similar could be done in your situation (button click event vs. get content event?).

Terry Aney

unread,

Apr 22, 2024, 8:08:03 AM4/22/24

to Excel-DNA

> I'm guessing something similar could be done in your situation (button click event vs. get content event?).

@Brian, I actually don't have an issue with ribbon button handlers that need to run asynchronously, I just use the ExcelAsyncUtil.QueueAsMacro( async () => pattern. The problem is for 'events' from Excel objects, whether it be ribbon or application and (as you said) expect synchronous code flow/results. You can see my pattern if you look at Ribbon_OnAction method from my repo. Essentially, all ribbon buttons use this as their `onAction` delegate, then pass the name of the method to call and if needed, parameters via | delimitation. It's similar to yours except that I use this entry point as a 'global' launch/exception catcher for all my ribbon buttons.

Govert van Drimmelen

unread,

Apr 22, 2024, 11:09:19 AM4/22/24

to Excel-DNA

Hi Terry,

Doing "GetDebugCalcEnginesAsync().GetAwaiter().GetResult();" will result in a synchronous, blocking call. This is not what you want running on the main thread (either in a ribbon callback, Excel event callback or inside QueueAsMacro.

The general pattern is that you have to quickly return from the ribbon callback or event. You cannot wait or block inside the ribbon callback, waiting for the slow async call(s) to complete first, and then return the result. You have to return something immediately (maybe some kind of empty menu or a label that indicates 'BUSY'). The only thing you can do is to start the async task (running on a different thread, typically a threadpool thread). Then, when the async task completes, you can call back into Excel to do some work based on the result. This call back to Excel will either call the ribbon, with 'Invalidate' or 'InvalidateControl' or talk to Excel through the COM object model. In both cases you should call Excel on the main thread only - so you can use QueueAsMacro from the worker thread, in order to get the code running on the main thread.

There are a few ways to do both the threadpool work, and the continuation after the work completes. But you have to have an awareness of what thread is running (call System.Threading.Thread.CurrentThread.ManagedThreadId - this will be 1 for the main thread, and something else for a threadpool thread).

In my example code earlier in the discussion, I handle a button click on the ribbon to start the async work, and when the async work is complete, I call QueueAsMacro to call Invalidate on the ribbon from the main thread. This Invalidate call causes the ribbon 'get' callback to run again, and this time I have a new result to respond with, thus updating the ribbon. One could do something similar with the Task.ContinueWith method, to schedule some work after the Task is completed. I used the async / await mechanism.

Now you might understand the problem with this code:

var task = Task.Run( () => GetDebugCalcEnginesAsync() );
var debugFiles = task.GetAwaiter().GetResult();

The second line will not return until the task is complete (that's what GetAwaiter().GetResult() does). When this runs, Excel is locked up (since the main thread is waiting for the task to complete). If anything tries to talk to Excel, or show a message box, or even call QueueAsMacro, it can't happen and you might get a deadlocked situation.

To answer some earlier questions you had:

1. Is Task.Run(()=>MethodAsync()) preferred over MethodAsync().GetAwaiter().GetResult()?

These are completely different - the one starts a task running on a threadpool thread, and the other blocks on the current thread until the task is complete, turning the async task into a synchronous call. You cannot use the second pattern in any of the callback situations.

2. "and add Debug.Assert checks if you have any doubt" ... is there some sort of easy pattern to implement to do this? Not really sure what/where I'm asserting.

You can call

Debug.Assert(System.Threading.Thread.CurrentThread.ManagedThreadId == 1);

near any code that will call back to Excel, either using the COM object model (Application, Range, etc) or using the ribbon interfaces (IRibbon.Invalidate / InvalidateControl).

Conversely, you can check that code that you expect to run on a threadpool thread is not running on the main thread using a similar check, with ManagedThreadId != 1.

-Govert

Terry Aney

unread,

Apr 24, 2024, 3:20:56 PM4/24/24

to Excel-DNA

OK, changing all my methods to synchronous and using Task.Run/QueueAsMacro. Last two questions I think:

1. I assume it is fine to have a code flow where I might have several 'QueueAsMacro' calls (application.StatusBar, .Cursor or ribbon.Invalidate()) sprinkled multiple times within one async function? Is it expensive? Mock sample below, take note of the helper ClearStatusBar.

2. I guess I'm a little confused on access to ActiveWorkbook and its properties (name, worksheets, etc.). Do I have to protect calls to that? Given code below, the WorkbookState.UpdateWorkbookAsync() method will access properties of the application. Is what I'm doing correct or do I need to changes the way I do it?

Thanks for all the explanations so far. Hopefully it helps others as well.

Govert van Drimmelen

unread,

Apr 24, 2024, 3:57:37 PM4/24/24

to exce...@googlegroups.com

Hi Terry,

This looks OK.
You need to access both the Excel COM object model and the ribbon from the main thread only – not directly inside your async Task. So both the calls that will refer to the (captured) ActiveWorkbook and the InvalidateRibbon() needs to be in QueueAsMacro context. I’m not sure what your UpdateWorkbookAsync does, but you need to be careful there. Also keep in mind that the workbook might no longer be the active one, or even open in Excel at all, by the time your async work is finished. That can be hard to deal with, but at least be aware and test a bit with a slow async call, quickly closing the workbook after pressing the button, and then seeing whether your code still runs OK or catches the right exceptions.

-Govert

From: exce...@googlegroups.com <exce...@googlegroups.com> On Behalf Of Terry Aney
Sent: Wednesday, April 24, 2024 9:21 PM
To: Excel-DNA <exce...@googlegroups.com>
Subject: [ExcelDna] Re: async Support in Ribbon Events

OK, changing all my methods to synchronous and using Task.Run/QueueAsMacro. Last two questions I think:

1. I assume it is fine to have a code flow where I might have several 'QueueAsMacro' calls (application.StatusBar, .Cursor or ribbon.Invalidate()) sprinkled multiple times within one async function? Is it expensive? Mock sample below, take note of the helper ClearStatusBar.

2. I guess I'm a little confused on access to ActiveWorkbook and its properties (name, worksheets, etc.). Do I have to protect calls to that? Given code below, the WorkbookState.UpdateWorkbookAsync() method will access properties of the application. Is what I'm doing correct or do I need to changes the way I do it?

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/7134fff3-35c6-43e7-a1ff-42c66309ef39n%40googlegroups.com.