ExcelAsyncHandle

Native Excel based async functions
Calling functions asynchronously can improve performance by allowing several calculations to run at the same time.
Excel 2010 (and obviously Excel 2013 as well) can call user-defined functions asynchronously.
link - microsoft.com/en-us/microsoft-365/blog/2010/01/27/programmability-improvements-in-excel-2010/
link - docs.microsoft.com/en-us/previous-versions/office/developer/office-2010/ff796219(v=office.14)


Excel 2007 Multi-Threading Options

In Excel 2007 you could run multiple user-defined functions at the same time by increasing the number of threads used in multiple-thread recalculations.
In Excel 2007 the number of threads can be changed at the Application Level and not at the function or add-in level.
link - docs.microsoft.com/en-us/office/client-developer/excel/asynchronous-user-defined-functions
link - docs.microsoft.com/en-us/office/client-developer/excel/multithreaded-recalculation-in-excel


Example 1 - Not Thread Safe

This function calls a method (with one parameter) that does return a Task.
This function will lock the Excel session and can use ALL the available calculation threads.
This function will not update when you press F9.

public static async void udf_MORETEXT(string sArgument1, 
                                      ExcelDna.Integration.ExcelAsyncHandle asyncHandle)
{
    try
    {
       object result = await Method_ToCall(sArgument1);
       asyncHandle.SetResult(result);
    }
    catch (System.Exception ex)
    {
       asyncHandle.SetException(ex);
    }
}

static async System.Threading.Tasks.Task<object> Method_ToCall(string sArgument1)
{
    DateTime start = DateTime.Now;
    DateTime end = DateTime.Now;

    await System.Threading.Tasks.Task.Run(() =>
    {
        start = DateTime.Now;
        string sText = "";
        for (int icount = 1; icount <= 50000; icount++)
        {
           sText = sText + "a";
        }

        end = DateTime.Now;
    });

    return start + " - " + (end - start).TotalMilliseconds;
}

Example 2 - Thread Safe

This function calls a method (with one parameter) that does return a Task.
This function will lock the Excel session and can use ALL the available calculation threads.
This function will not update when you press F9.

[ExcelDna.Integration.ExcelFunction(IsExceptionSafe = true, 
                                    IsMacroType = false,
                                    IsThreadSafe = true)]
public static async void udf_MORETEXT_TS(string sArgument1,
                                         ExcelDna.Integration.ExcelAsyncHandle asyncHandle)
{
   try
   {
      object result = await Method_ToCall(sArgument1);
      asyncHandle.SetResult(result);
   }
   catch (System.Exception ex)
   {
      asyncHandle.SetException(ex);
   }
}


Example 3

This function calls a method (with no parameters) that does not return a Task.
This function will not update when you press F9.

public static async void udf_SOMETEXT5(string sArgument1, 
                                       ExcelDna.Integration.ExcelAsyncHandle asyncHandle)
{
   try
   {
      object result = await System.Threading.Tasks.Task.Run( Method_ToCall );
      asyncHandle.SetResult(result);
   }
   catch (System.Exception ex)
   {
      asyncHandle.SetException(ex);
   }
}

static Method_ToCall()
{
   await System.Threading.Tasks.Task.Run(() => {
    });
   return "some text";
}

Example 4

This function calls a method (with one parameter) that does not return a Task.
This function will not update when you press F9.

public static async void udf_SOMETEXT5(string sArgument1, 
                                       ExcelDna.Integration.ExcelAsyncHandle asyncHandle)
{
   try
   {
      object result = await System.Threading.Tasks.Task.Run( () => Method_ToCall(sArgument1) );
      asyncHandle.SetResult(result);
   }
   catch (System.Exception ex)
   {
      asyncHandle.SetException(ex);
   }
}

static Method_ToCall(string sArgument1)
{
   await System.Threading.Tasks.Task.Run(() => {
    });
   return "some text";
}


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