Asynch function calls aren't retrieving new data

May 12, 2015 at 7:57 PM
Hi -

I am using ExcelAsyncUtil.Observe to call an async function that makes a service call using async/await and returns the value to the cell. This works the first time you enter the formula but I can't figure out how to get the call to happen again if the data changes but the parameters are the same. For example, pasting the formula to a new cell, closing and re-opening the worksheet, re-calculating the worksheet or cells, etc... Only if I change a parameter will it make the call again. I read on one forum post that the output is assumed the same if the input hasn't changed - but that is almost never the case for us. How can we work around that or avoid it while maintaining async functionality? Is there a way to force a cell to recalculate?

Thanks!
Coordinator
May 12, 2015 at 9:16 PM
In your call to ExcelAsyncUtil.Observe, you are providing (a function that returns) an IExcelObservable, whose Subscribe method will be called (exactly once) passing you an IExcelObserver. Whenever the back-end data changes, you call IExcelObserver.OnNext(newValue) on this observer, and the cell will update.

If you just want a single-value async function, you can use ExcelAsyncUtil.Run() instead. After the function returns it will reset, so will run the inside function again next time the function calculates.

-Govert
May 12, 2015 at 9:50 PM
Edited May 12, 2015 at 9:57 PM
Thanks so much for your quick response. I started by trying ExcelASync.Run() but ran into the same issue so I moved to the Observable structure. I only need the Run functionality for now. Here is a simple sample of what I'm trying to do:
        [ExcelFunction]
        public static object GetAsyncData(string param1, DateTime param2, string param3)
        {
            return ExcelAsyncUtil.Run("GetAsyncData", new object[] { param1, param2, param3 }, (() =>
            {
                var webService = new WebService();
                var data = webService.getData(param1, param2, param3);
                return data.someString;
            }));
        }
When I put the formula that calls to this function into a cell, it works properly. If I then copy and paste that cell to the next cell, GetAsyncData gets called as expected. However, the anonymous method that ExcelAsyncUtil.Run() is supposed to run (the call to the web service and returning of the appropriate data) is never run and the old value is returned. When I do the same thing without Run() using a standard synchronous ExcelFunction, it works perfectly. The problem is ExcelASyncUtil.Run() is not running the anonymous method more than once with the same parameters.

-Eric
Coordinator
May 12, 2015 at 10:00 PM
Hi Eric,

What version of Excel are you running on? Excel 2010 RTM has various RTD-related bugs, which Excel-DNA is only partially able to work around. This issue might relate: http://exceldna.codeplex.com/workitem/9463

If you're not on Excel 2010 RTM, I'd need to have a closer look. The intended functionality is as you understand it - that after the inner delegate has completed, and the wrapper function been called again, everything is reset, and so will on the next calc run the inner function again.

-Govert
May 13, 2015 at 1:56 PM
Hey Govert -

We aren't using Excel RTM. The exact version of office is: Office 2010 Professional Plus version 14.0.4760.1000 - 32 bit.

I have put together a sample project demonstrating the behavior I am describing. I don't see a file attachment option here, but there is very little code. 2 small classes. I will paste them below. Put a breakpoint inside GetMyData() and inside the delegate on the Console.WriteLine(). If you put the formula in a cell, both break points will hit. If you then copy and paste that cell to the next cell, the first breakpoint hits but not the second. At least for me. Same thing happens if you control-f9 to recalc. If you have a minute to take a look, I would appreciate it - if not, maybe you could direct me to any relevant portions of excel-DNA's code and I can take a look myself.

Thanks,
Eric
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ExcelDna.Integration;

namespace ExcelDnaSample
{
    public class ExcelDnaSample
    {

        [ExcelFunction]
        public static object GetMyData(string param1, DateTime param2, string param3)
        {
            return ExcelAsyncUtil.Run("GetMyData", new object[] { param1, param2, param3 }, (() =>
            {
                Console.WriteLine("I am in the delegate.");
                return WebService.GetData(param1, param2, param3);
            }));
        }
    }
}

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ExcelDnaSample
{
    public class WebService
    {
        private static int counter;

        public static int GetData(string param1, DateTime param2, string param3)
        {
            counter = counter + 1;
            return counter;
        }
    }
}

Coordinator
May 13, 2015 at 3:06 PM
Hi Eric,

You are exactly on the problematic Excel version - the Excel 2010 Release to Manufacturing (RTM) version was 14.0.4760.1000.
I'd suggest you install the Office 2010 SP1 - that should fix the Excel bugs that cause this problem.
        // RTM was:                                     14.0.4760.1000
        // the Excel version with the hotfix* is:       14.0.5128.5000
        // SP1 where the bug is also fixed has version: 14.0.6129.5000
        // * according to http://support.microsoft.com/kb/2405840
As far as I know, I work around this problem even for the problematic Excel 2010 version, but only in the source versions you can get from GitHub: https://github.com/Excel-DNA/ExcelDna In Excel-DNA version 0.32 there was still a problem, as described in the other issue - http://exceldna.codeplex.com/workitem/9463.

-Govert
May 13, 2015 at 3:18 PM
Apologies, I didn't think that was the RTM version. I'll give your suggestions a shot.

Thanks for the timely help.

Eric