Method parameter as native Excel function

Mar 21, 2013 at 3:09 PM
Edited Mar 21, 2013 at 3:12 PM
Hi ,

Let's concider this method

[ExcelFunction(Description = "The magic function")]
    public static object ShowMeMagic(DateTime date)
    {
        object runResult = ExcelAsyncUtil.Run("MAGIC", date,
                                              delegate
                                                  {
                                                      Thread.Sleep(2000);

                                                      if (date >= DateTime.Today)
                                                      {
                                                          return "it will be magic ! "+date;
                                                      }
                                                      return "it was magic ! "+date;
                                                  });

        return runResult;
    }
I made an asynchronous excel function with Excel Dna. It works fine when passing a date as value.
But when when I test it with TODAY() and TODAY()-1 as parameters in two separe cells it turns into an infinite evaluation.
Coordinator
Mar 21, 2013 at 6:06 PM
Hi,

The TODAY() function is a 'volatile' function. These interact badly with the async feature. See the following discussions:
I don't have a plan for making volatile and async work better together. If you need a varying input to your function, you might consider using the Reactive Extensions approach instead.

-Govert
Mar 21, 2013 at 6:06 PM
I should have read more documentation before asking.

I Think It's related to excel not to Excel-DNA. As Today() is VOLATILE it will reevaluated after every little change in the sheet.

So I created my own UN-VOLATILE Today function and it's working fine now.

But I'm still open to a better suggestion.
Mar 21, 2013 at 6:09 PM
Edited Mar 21, 2013 at 6:10 PM
I think we both posted something the same time.

Thank you for your answer !
Mar 21, 2013 at 6:25 PM
I think we posted something the same time.

Thank you for your answer !