ExcelAsyncUtil.Run

Excel 2002 and later. Available with .NET 2.0.
Accessing Excel from a different thread is not possible.
We can get around this by placing the request into a queue that can execute on the main Excel thread when it is ready.
This code will run on a ThreadPool thread
The RTD server allows Excel-DNA to notify Excel that a function should be recalculated - typically after the async task is complete.
You can continue interacting with the Excel UI while the function executes.


ExcelAsyncUtil.Run - Parameters

functionName (string) - identifies this async function.
parameters (object) - includes all the parameters to the function.
This can be a single object (string, or an object array of parameters.
function (function/delegate) - a delegate that will be evaluated asynchronously.


public static object udf_SLEEP(string sOneArgument) 
{
    return ExcelDna.Integration.ExcelAsyncUtil.Run("udf_SLEEP",
                                                   sOneArgument,
    delegate {
        System.Threading.Thread.Sleep(3000);
        return sOneArgument);
    });
}


public static object udf_SLEEP(string sOneArgument) 
{
    return ExcelDna.Integration.ExcelAsyncUtil.Run("Async1",
                                                   sOneArgument,
    delegate {
        Debug.Print("{1:HH:mm:ss.fff} Sleeping for {0} ms", ms, System.DateTime.Now);
        Thread.Sleep(int.Parse(ms));

        Debug.Print("{1:HH:mm:ss.fff} Done sleeping {0} ms", ms, System.DateTime.Now);
        return "Woke Up at " + DateTime.Now.ToString("1:HH:mm:ss.fff");
    });
}

public static object Async2(string sFirstArgument, string sSecondArgument) 
{
    return ExcelDna.Integration.ExcelAsyncUtil.Run("Async2",
                                                   new object[] {sFirstArgument, sSecondArgument},
    delegate {
        Debug.Print("{1:HH:mm:ss.fff} Sleeping for {0} ms", ms, System.DateTime.Now);
        Thread.Sleep(int.Parse(ms));

        Debug.Print("{1:HH:mm:ss.fff} Done sleeping {0} ms", ms, System.DateTime.Now);
        return "Woke Up at " + DateTime.Now.ToString("1:HH:mm:ss.fff");
    });
}

Behind the Scenes

You put an async function in a formula.
Excel recalculates, calling your async function which internally calls ExcelAsyncUtil.Run in Excel-DNA.
ExcelAsyncUtil.Run creates an RTD topic (using the function name and parameter info you pass as the first the arguments to ExcelAsyncUtil.Run)
ExcelAsyncUtil.Run starts your async work (using the delegate passed as the third argument) and associates that with the RTD topic.
ExcelAsyncUtil.Run returns #N/A to your UDF while the calculation continues.
Your UDF returns the #N/A back to the Excel sheet.
Now your work completes, and Excel-DNA signals to Excel that the RTD topic has been updated.
Excel marks the cell which had that RTD topic in as dirty.
Excel starts to recalculate, causing that cell to recalculated, causing your UDF to be called.
The UDF calls ExcelAsyncUtil.Run with the same topic information as before.
ExcelAsyncUtil.Run find the topic and completed value that it stored from your function. It then returns the value directly (instead of #N/A as before).
Your UDF function received the result value, and returns that to the sheet.
Because ExcelAsyncUtil.Run did not call into Excel's RTD function again, the RTD topics gets cleaned up internally by Excel.



Example 1

This function will take a few seconds to run and will run on a different thread while it is calculating.
This function will not lock the Excel session.
While this function is calculating "#N/A" will be displayed in the cell.
This function will not update when you press F9.

public static object udf_SOMETEXT3() 
{
   return ExcelDna.Integration.ExcelAsyncUtil.Run("udf_SOMETEXT3",
                                                  null,
   delegate {
      string sText = "";
      for (int icount = 1; icount <= 200000; icount++)
      {
         sText = sText + "a";
      }
      return "some text";
   });
}

Example 2

This function will take 3 seconds to run and will run on a different thread while it is calculating.
This function will not lock the Excel session.
While this function is calculating "#N/A" will be displayed in the cell.
This function will not update when you press F9.

public static object udf_SOMETEXT4(string sArgument1) 
{
   return System.Convert.ToString(ExcelDna.Integration.ExcelAsyncUtil.Run(
      "udf_SOMETEXT4",
      sArgument1,
      delegate {
          System.Threading.Thread.Sleep(3000);
          return sArgument1;
      })
      );
}

Links

link - docs.microsoft.com/en-gb/previous-versions/office/developer/office-xp/aa140060(v=office.10)
link - docs.microsoft.com/en-gb/previous-versions/office/developer/office-xp/aa140061(v=office.10)
link - docs.microsoft.com/en-gb/previous-versions/office/developer/office-xp/aa140059(v=office.10)
link - docs.microsoft.com/en-gb/previous-versions/office/developer/office-xp/aa140058(v=office.10)
link - docs.excel-dna.net/asynchronous-functions/


© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited TopPrevNext