Re: ExcelDNA - ExcelFunction is called multiple times when we calling a ExcelAsyncUtil.Run() async menthod in ExcelFunction.

[Govert van Drimmelen]

Hi Marik,

The combination of async with array functions is problematic, due to Excel's behaviour with array RTD functions.

I don't have a good suggestion to work around this. 

One approach is to cache the async function's result, so that the multiple calls from Excel can be handled fairly efficiently.

Another pattern is to split the function into an async function that you put in a single cell, and that returns a handle to an internal data structure with the results. Then make an array function that takes the handle and returns the full array (or single cells) from the internal structure. It's a bit harder to use, but should avoid this problem. 

Regards,

Govert

On Monday, 24 August 2015 15:07:40 UTC+2, Marikkannan K wrote:

Hi,

Excel Function is called multiple times When i calling a method asynchronously using  ExcelAsyncUtil.Run() method from my Excel Function. If i removed the ExcelAsyncUtil.Run() Async method call, the Excel Function is calling Only one Time.

Please find the below code snippets.

[ExcelFunction(Name = "GetEmployeeList", Description = "Returns an array of fields",Category = "IT", HelpTopic = "help.chm!1000",IsMacroType = true)]

 public static object[,] UdfMetaDataFunction

         ([ExcelArgument(AllowReference = false, Name = "Column_name", Description = "is an array or range of cells")]               

            object columnName,[ExcelArgument(Name = "Hide_additional_info", Description = "is a logical value to hide Metadata about the fields. TRUE hides Metadata; FALSE or omitted does not hide Metadata")] bool hideAdditionalInfo)

        {

 

            object[,] array = new object[1, 1];

            array[0, 0] = "Loading";

// If I Removed the ExcelAsyncUtil.Run() method, then Excel function is calling one time only. 

                      

   var result = ExcelAsyncUtil.Run("GetFunctionMetaData", new[] { columnName, hideAdditionalInfo }, () =>GetFunctionMetaData(columnName, hideAdditionalInfo));

            if (result.Equals(ExcelError.ExcelErrorNA))

            {

                return FunctionHelper.DisplayErrorTemplateMessage(errorarray, "Loading...");

            }

 

            return array;

 

        }

     MethodImpl(MethodImplOptions.Synchronized)]

      public static OutPutParser GetFunctionMetaData(object headers, bool additionalInfo)

      {

         //calling service to getData

      }

Kindly help me on this issue. 

Thanks,

Marik