Async UDF

Nov 2, 2012 at 9:24 PM

So I am trying to get a UDF to run async. Here is a chunk of sample code that I am working with:

        [ExcelFunction(Name = "TESTFUNC",
        Description = "TESTING A FUNC",
        Category = "TEST")]
        public static object Func([ExcelArgument(Description = "Test Desc", Name = "TestName", AllowReference = true)] string output)
        {
            return ExcelAsyncUtil.Run("Func", null, delegate
            {
                return output;
            });
        }

        [ExcelFunction(Name = "TESTFUNC2",
        Description = "TESTING A FUNC",
        Category = "TEST")]
        public static object Func2()
        {
            return ExcelAsyncUtil.Run("Func", null, delegate
            {
                Random r = new Random();
                return r.Next(0,100000);
            });
        }

Both of them work fine if I type the function out and hit enter but when I try to drag either of them they all return the same value. This is also the case with my real code.

Am I missing something or is there another way to do this?

Coordinator
Nov 2, 2012 at 10:01 PM

The second argument to the ExcelDnaUtil.Run call should be the parameters to your function:

       [ExcelFunction(Name = "TESTFUNC",
        Description = "TESTING A FUNC",
        Category = "TEST")]
        public static object Func([ExcelArgument(Description = "Test Desc", Name = "TestName", AllowReference = true)] string output)
        {
            return ExcelAsyncUtil.Run("Func", output, delegate
            {
                return output;
            });
        }

The second function is 'volatile' and doesn't match the ExcelAsyncUtil.Run model. Async decouples the recalculate from the result actually returning, so the idea of getting a different value every time your recalculate doesn't make sense. The right way is probably to make an Observable and hook up via ExcelAsyncUtil.Subscribe(...) or make an RTD server.  

If you mean to have a different value for each calling cell, then you can pass the caller in as the second parameter, as in 

[ExcelFunction(Name = "TESTFUNC2",
        Description = "TESTING A FUNC",
        Category = "TEST")]
        public static object Func2()
        {
            object caller = XlCall.Excel(XlCall.xlfCaller);
            return ExcelAsyncUtil.Run("Func", caller, delegate
            {
                Random r = new Random();
                return r.Next(0,100000);
            });
        }

-Govert

Nov 2, 2012 at 11:15 PM

First, thank you very much for your prompt reply.

I am looking to have a different value for each cell that calls this funciton exists in. I can achieve this if I type the formula into each cell but when I drag them in using the built in excel functionallity for populating cells (the little black handle in the botton leftcorner of a selected cell). 

Maybe I'm missing someting (I started using exceldna 2 days ago so maybe I am) but how does passing in the caller fix things?

Where can I find some sample code for ExcelAsyncUtil.Subscribe(...) and the RTD server?

 

Thanks,

Russ

Coordinator
Nov 3, 2012 at 9:43 PM

The values passed to the second parameter are used to distinguish different 'instances' of the async call. Hence passing in the caller as in my example will ensure that different cells are treated as different instances of the async call. I think when dragging the formula to many cells, that actually execute concurrently, which allows you to see the issue. Entering them one-by-one means there is enough time for the function to complete.

There are some examples in the distribution under Distribution\Samples\Async, and here: http://exceldna.codeplex.com/wikipage?title=Reactive%20Extensions%20for%20Excel&referringTitle=Documentation (I means ExcelAsyncUtil.Observe(...)).

-Govert