Asynchronous UDF screen refresh issue in manual mode

[Leonid Ilyevsky]

I am working on a project to provide C# UDFs that call webservice to get the data. Below is my simple prototype. The Excel version is 2010.

Everything works fine when Excel formula calculation is in automatic mode, even when I introduce simulated delay of 5 seconds on the webservice side.

However, I need to make it work properly in the manual mode: I cannot allow it to make a remote call every time something is changed in the spreadsheet; my users are going to hit F9 when ready.

Here is what is happening in the manual mode. When I change the data and press F9, I see in the server log that the calls came and were returned, but on the screen the formula fields show as "#N/A". Then I press F9 again, and at that time I see the expected values.

Another test: I introduce delays, and I press F9 second time when some values did not come from the server yet. Then obviously I see some good data, and the rest as "#N/A". Then I press F9 again, until I get everything on the screen.

So the question is: where is the problem and how I can fix it?
Is it Excel problem that can be fixed by configuration?
Or do I need to do some complicated workaround on C# side ?

===============================================
using ExcelDna.Integration;

namespace MyLibrary
{
    public class AsyncTestAddIn : IExcelAddIn
    {
        private static mcmwsli.WebService1 service = new mcmwsli.WebService1();

        private static StreamWriter log = new StreamWriter("C:/temp/logfile.txt");

        public void AutoOpen()
        {
            ThreadPool.SetMinThreads(256, 256);
            ExcelAsyncUtil.Initialize();
            ExcelIntegration.RegisterUnhandledExceptionHandler(
                ex => "!!! EXCEPTION: " + ex.ToString());
        }

        public void AutoClose()
        {
        }

        public static object RemoteFunction(double x)
        {
             return ExcelAsyncUtil.Run("RemoteFunction", x, delegate
            {
              
                log.WriteLine(DateTime.Now.ToString("HH:mm:ss.fff") + " Remote Call: " + x.ToString());
                double y = service.sin(x);
                log.WriteLine(DateTime.Now.ToString("HH:mm:ss.fff") + " Remote Done: " + x.ToString());
                log.Flush();
                return y;
            });
        }
    }
}

[Naju Mancheril]

I think this is a consequence of how RTD works in Excel. If you are in manual mode, I don't think new values (from the server, etc) get pushed into the sheet until you hit F9. The order of operations you are seeing is:

User changes data
User hits F9

Excel runs the [ExcelFunction] part of your function. It starts the async part and returns NA (since it doesn't have an answer already).

Async stuff runs and queries web server. The returned response is put into the "refresh data" and passed to Excel.

User hits F9 again.
Excel runs the [ExcelFunction] part of your function. It sees that it HAS an answer waiting and returns that.

Is the manual calculation a firm requirement for your project, or is it just a means to some other goal (e.g. not overloading the server, etc)? You may want to make your RTD async implementation worry about that, rather than mess with Excel's calculation model. Does that sound like a better approach?

[Leonid Ilyevsky]

Yes, regardless of these issues on the Excel itself, I am planning to implement some smart logic on C# add-iin and also on my Java webservice, to conflate requests, etc.
However, this Excel behavior is a problem. Imagine hundreds of cells with these formula calls, depending on hundreds of ther cells, etc. The user will take some time updating a number of cells. If it is in automatic mode, it will do the calls all the time user is working on his thing. They really want to refresh only when they press F9.

Currently my users enjoj this behavior with Bloomberg stuff, I wonder how that is done. I saw on some forum somebody asked the same question, but no clear answer.
This is why they expect that I will provide similar functionality.

[Govert van Drimmelen]

Hi Leonid,

I'm not sure how you want it to work. 

Do you want the sheet to refresh itself when the async call completes, even though it is in manual mode?

You could do that by running a macro to recalc (triggered by ExcelAsyncUtil.QueueMacro) when the data comes back. That way you can wait for all the outstanding async results to come back, then simulate an F9 recalc that updates all the cells.

Another approach is to stick to automatic mode, but have a switch (from a ribbon or shortcut key) that enables / disables the async back-end calls. That way the user can put the sheet in 'editing' mode where back-end calls are suspended, then go back to 'live' mode when their done.

I have no access to Bloomberg and have no idea how their add-ins work. But I'm pretty sure Excel-DNA now has all the necessary plumbing for building any behaviour that Excel makes possible. It's sometimes tricky to decide how ti should work, and Bloomberg has sometimes made awkward trade-offs I'd steer away from.

Another approach, since you are on Excel 2010, is to use the native Excel async feature introduced in that version. I believe the works a bit differently regarding manual updates.

I have added the basic support in Excel-DNA - you need to get post v0.30 check-in from CodePlex: https://exceldna.codeplex.com/SourceControl/latest

There is a NativeAsync sample in the distribution. You'll have to change your functions a bit too - (I'd suggest building all your async stuff on the .NET 4 Task-based APIs. That's what I'm most likely to improve the integration with in future.)

Any more questions or feedback on what you find or decide in this regards will be very welcome.

Cheers,

Govert

[Leonid Ilyevsky]

Thanks Govert, this is encouraging, I have some hope now.
You understood me correctly: I want users to prepare their data without any back-end calls going on, then press a button and get all the data on the sheet. Obviously getting data will take some time, so they will see some message in the cells that are pending, until the numbers come.
I think I will first try your suggestion to make a recalc trigger by using ExcelAsyncUtil.QueueMacro. I would appreciate if you can give me a little snippet. My problem is, for all my long career, my bread and butter was C/C++ and then Java since its launch in 1995, so I am very new to the Microsoft world.
Also, I wonder if in my C# code I can get the following information from the front end:
- catch an event when user presses F9
- get some information about back-end calls triggered by that, at least the number of calls

This way I can implement logic to detect when all results are back. I think I still want to trigger the recalc multiple times while data is coming, so the user will see that it is working, that more and more fields are getting data. But at some point I need to stop doing it.
However, if this information is difficult to get, I can do it some other way. I can track the number of outstanding calls at any time, and trigger the recalc until it becomes zero.

Thanks for your help!

[Leonid Ilyevsky]

Govert,

Another question. Is it possible, in my C# Excel function, to figure out what spreadsheet cell the call came from, and then initiate the recalc of that one field? The recalc should happen after the result is returned to excel, so it has to be in another thread and delayed; is there a way to know when the results are returned to excel?

[Leonid Ilyevsky]

I came up with a simple test that demonstrates the issue, see the code below. Since remote webservice call is not essential, I replaced it by delayed (100 ms) calculation.
To run the test, you put on the spreadsheet in the A column a progression of numbers, say, A1=0.1, then A2 =A1 + 0.1, etc. In B1 you put "=LocalFunction(A1)", and so on.

So maybe somebody can  enhance my code so that in manual mode it will automatically display results after only one push of a button.

Now I actually think that current Excel behavior is a bug, or at least a missing feature. In the automatic mode it knows how to wait for the results, and it displays them as they come. So why doesn't it do the same in manual mode? When user wants to recalculate in manual mode, it actually means to recalculate all.

This is what I noticed. In manual mode, I change my data, and the "Calculate" button appears on the status bar. I press it (or F9), and some data comes, but not all. The "Calculate" button is still there. I hit it again, and this time all my data is displayed, and I see the "Calculate" button disappeared.
This means that excel actually knows whether any calculations are pending, or everything is done. So, they can easily solve this problem. Would you know if Excel 2013 behaves differently compare to 2010?

I also found another trick. I edit the sheet in manual mode, then, instead of pressing F9, I just switch to automatic. All data comes as expected. I don't think the users would like this workaround. Are there any ways to do it programmatically?

==============================

using System;
using System.Threading;
using System.IO;

using ExcelDna.Integration;

namespace MyLibrary
{
    public class AsyncTestAddIn : IExcelAddIn
    {
     

        public void AutoOpen()
        {
            ThreadPool.SetMinThreads(256, 256);
            ExcelAsyncUtil.Initialize();
            ExcelIntegration.RegisterUnhandledExceptionHandler(
                ex => "!!! EXCEPTION: " + ex.ToString());
        }

        public void AutoClose()
        {
        }

        [ExcelFunction(IsMacroType = true)]
        public static object LocalFunction(double x)

        {
           return ExcelAsyncUtil.Run("RemoteFunction", x, delegate
           {

               Thread.Sleep(100);
               double y = Math.Sin(x);
               return y;
           });
        }

    }
}

[Naju Mancheril]

Here is an example.

Signature Fix
You need to fix the TODO around ConnectData (I've modified the ExcelDna source code on my machine, and in the process, changed the signature of this method). This should be an easy thing to do. Post back if you have trouble.

Code

The first source file provides a ThreadPoolQueue class, which is useful for scheduling.

The second source file contains the RTD code. It inherits from ExcelRtdServer (rather than using the ExcelAsyncUtil helper function). Both ways are supported by ExcelDna. The GetData method should be replaced by something that calls out to your server.

Excel Functions and Menu Items

The worksheet function is called RemoteFunction. It takes two string params. Feel free to make them doubles. Excel's RTD interface only understands strings, so you'll need to do .ToString() and parse them back (in ConnectData or GetData).

New (distinct!) function calls can be refreshed using the Update New menu item (they will show Ready... until they are refreshed).

All function calls can be refreshed using the Update All menu item.

Workbook

The attached workbook has some examples. It can be used to test that Update New only updates a cell the first time you click it. Subsequent calls are unmodified.

You should use automatic calculation for all this to work.

[Leonid Ilyevsky]

ngm,

Thanks a lot, I am making progress with your example. Here is how I modified the TODO piece:

 protected override object ConnectData(ExcelRtdServer.Topic topic, IList<string> topicInfo, ref bool newValues)
    {
        Thread.Sleep(1000);
        string result = "";
        foreach (var t in topicInfo)
        {
            result = result + ":" + t;
        }
        newValues = true;
        return result;
    }

It is actually working in manual mode too. The "Calculate" button is visible while calculation is in progress, and it disappears by itself when it is done. It actually delivers all the results to the sheet without additional button clicks.

One more critical problem I need to solve. As you can see, I have a 1 second delay to simulate remote call. The refresh in the sheet takes essential time, as if only 4 or 5 threads are available for this job. In my original test I solved this by calling "ThreadPool.SetMinThreads(256, 256);" in the AutoOpen() method. In your example, I put the same call in the ServerStart(), but it does not help. Either this is the wrong place to put the call, or this is the wrong thread pool. Any suggestions?

Also, I would appreciate if you can point me to some documentation explaining in details this whole architecture. I am not used to work in the dark :)

[Leonid Ilyevsky]

Correction to my threading problem: I actually timed it, and now I strongly believe that the RTD is using only one thread. I also see some discussions in the forums saying the same thing. Is this still true? Does it make any sense? This defeats the whole idea of "real time".

[Govert van Drimmelen]

Hi Leonid,

You can't do the slow work in the ServerStart or ConnectData methods. They will run on Excel's (single) main thread. That Thread.Sleeep is basically suspending all of Excel for a second.

-Govert

[Naju Mancheril]

That's not correct.

ConnectData is invoked once per topic by the Excel main thread. If you have N function calls, you will make Excel freeze for N seconds.

The point of the original code was to put ALL topics into a dictionary (the inserting happens on the main thread, but should be fast) and then fire off a worker thread (via ThreadPoolQueue) to actually send the request and get the response. Further, that worker thread will be able to send one request for a large batch of topics.

You should put your web server request / response / waiting code inside GetData only.

Are you unhappy with the menu clicking solution? I didn't test any of this stuff in manual mode, so I'm not sure how it will work...

[Naju Mancheril]

Hi,

Here's how to put something like your example into GetData:

    private int generationCount=0;

    private Dictionary<Topic, object> GetData(List<KeyValuePair<Topic, IList<string>>> request) {
      Thread.Sleep(5000);
      var data=new Dictionary<Topic, object>();
      var gen=++generationCount;
      foreach(var kv in request) {
        data[kv.Key]=gen+": "+string.Join(":", kv.Value);
      }
      return data;
    }

Feel free to post back if you have other questions (how to get more parallelism, send multiple requests to the web server, etc).

[Leonid Ilyevsky]

Thanks guys. I actually figured it out and I did exactly what you suggested, put the sleep and calculate values in GetData, and here is my ConnectData:

protected override object ConnectData(ExcelRtdServer.Topic topic, IList<string> topicInfo, ref bool newValues)
    {

        Add(topic, topicInfo);
        newValues = true;
        return "Ready ...";
    }

It works as you describe, and I see I can now implement multithreading in the GetData. However, it does not solve the usage problem; in fact, the usage is now worse than in my original program:

- in manual mode it does not work at all
- when input data is changed, I see "Ready..." and nothing is going on
- to actually trigger the back-end calculations, I have to go to  Remote => Update ... menu items,too many clicks already (can this actually be mapped to a button?)
- only after I do this "Update all", the time starts ticking, and data shows up after the delay (I set it to 10 seconds to be obvious)

On the other hand, my original program was almost a perfect solution for what I need, with only one issue: how to force Excel to pick up the results when they are ready, in manual mode. This problem is very strange, because, as I mentioned before, I see that Excel (in manual mode) clearly knows when results are pending and when they are all done: the disappearing "Calculate" button proves it. But it still wants user to press it. Ideally, this should be fixed in Excel. What do you think?
Or maybe we can do something in C# add-in? Let say, if I can get a status from Excel that calculation is pending, and trigger recalc, then I can do it constantly with some interval. Is this possible?

[Leonid Ilyevsky]

Govert,

In your latest version I see an overloaded ExcelDna.Integration.Run method:

public static object Run(string callerFunctionName, object callerParameters, ExcelFuncAsyncHandle asyncFunc);

Maybe this is what I need? I guess, I effectively use the old one when I do

return ExcelAsyncUtil.Run("RemoteFunction", x, delegate
           {

.............................

If indeed I need to use the new method, how do I do it? How I create an instance of ExcelFuncAsyncHandle?

[Leonid Ilyevsky]

Guys,

Thanks again for your help. I fugured this is indeed Microsoft issue, and I doubt we can do anything about it. Even Bloomberg cannot do it, to the contrary of what I thought previously.
After all this frustration I decided to play with the Bloomberg UDFs to see for myself, and I found that they have exact same problem in manual mode.
Our users did not have this impression because most of those functions actually are procedures that populate other cells in the book, and so they get the data that they need. But I noticed the "#N/A" or something like that in the cell where the formula is.