Function Types

There are two types of functions that you can create wit Excel-DNA.
Asynchronous Functions - Excel 2010 and later
RTD Functions - Excel 2002 and later



Asynchronous Functions

ExcelAsyncUtil.Observe
Requires .NET 4.5.
Use the .NET 4.0 Thread class.
link - docs.excel-dna.net/asynchronous-functions-with-tasks/
link - docs.microsoft.com/en-us/office/client-developer/excel/asynchronous-user-defined-functions
Only available with .NET 4.0
Excel 2010 and later
Cannot interact with Excel while the function executes
Can continue with other parts of the calculation tree while the function executes.
There are a number of different ways these can be called/used
observer pattern
asnc/await (.NET 4.5)


These take an ExcelAsyncHandle as the parameter and have a return type of "void".
This does not let you interact with Excel while the function is executing.
Excel can just continue with different parts of the sheet calculation while the function is executing.



Task.Factory.StartNew


namespace ExcelDNALibrary 
{
   public class Class1 : ExcelDna.Integration.IExcelAddIn
   {
      void ExcelDna.Integration.IExcelAddIn.AutoOpen()
      {
         ExcelDna.Integration.ExcelIntegration.RegisterUnhandledExceptionHandler();
         ExcelDna.Integration.ExcelIntegration.Initialize();
      }

      void ExcelDna.Integration.IExcelAddIn.AutoClose()
      {
         ExcelDna.Integration.ExcelIntegration.Uninitialize();
      }

      [ExcelDna.Integration.ExcelFunction(Description="returns the HTML string for a particular URL", Name="MyWebsiteString")]
      public static string MyWebsiteString([ExcelDna.Integration.ExcelArgument(Description=@"a website URL")] string sURL)
      {
         return ExcelDna.Integration.ExcelAsyncUtil.Observe("MyWebsiteString",
                                                            sURL,
                                                            delegate {
                value = MyHelperClass.GetStringAsync(sURL).ToExcelObservable();
                return value;
             }
      }
   }
}


public static void Run2() 
{
   Task.Factory.StartNew( () => {
      Thread.Sleep(2000);
      var sc = new ExcelSynchronizationContext();

      sc.Post(delegate {
         var app = (Application)ExcelDna.Util.Application;
         app.Cells[1,1] = String.Format("Done at {0}", System.DateTime.Now);
      }, null);

      Thread.Sleep(2000);

      sc.Post(delegate {
         new ExcelReference(3,3,3,3).SetValue(String.Format("Done at {0}", System.DateTime.Now));

         // update the status bar
         ExcelDna.Integration.XlCall.Excel(ExcelDna.Integration.XlCall.xlcMessage, true, "Hello");
      }, null);

      Thread.Sleep(2000);

      sc.Post(delegate {
         // cancel the status bar
         ExcelDna.Integration.XlCall.Excel(ExcelDna.Integration.XlCall.xlcMessage, false);
      }, null);

}



RTD Functions

ExcelAsyncUtil.Run
Requires .NET 2.0
Uses a Threadpool work item
Available with .NET 2.0
Excel 2002 and later
RTD Server, registration free
Can interact with Excel while the function executes
This uses an internal RTD server internally.
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.


This allows you to continue interacting with Excel while the function executes.
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/


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 ExcelAsyncUtil.Run("udf_SLEEP",
                              sOneArgument,
    delegate {
        System.Threading.Thread.Sleep(3000);
        return sOneArgument);
    });
}


public static object udf_SLEEP(string sOneArgument) 
{
    return 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 ms) 
{
    return 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.



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