how to refresh when using exceldna rtd

[Deepak Krishna]

Hi Govert,

I am implementing ExcelDna RTD server. When I enter a formula in a cell, I can see that output from

ConnectData

But when i double click on the same cell and press enter (basically i'm refreshing) I don't see any log from the above function.

How to make refresh work this way when using ExcelDna RTD server.

Before when i was using ExcelAsyncUtil.Run I had not had this problem.

 

[Govert van Drimmelen]

Hi Deepak,

You refresh the data in an RTD server by calling Topic.UpdateValue(...)

'Refreshing' the formula by editing the cell won't work, since Excel keeps the same topic, assuming you have not changed the input parameters that you pass to XlCall.RTD(...). 

ExcelAsyncUtil.Run internally 'completes' the RTD topic, by making sure that when the UDF gets called after the topic update, the internal call to RTD is skipped. That causes the topic to get disconnected, since the async call is 'done'.

Excel expects an RTD server to provide a stream of many values (real-time data) to Excel, so every time you call Topic.UpdateValue(...) is will update the sheet.

Implementing an async single-call function using RTD is quite a bit of work - that what ExcelAsyncUtil.Run(...) does for you.

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Deepak Krishna [kr...@sentieo.com]
Sent: 10 March 2016 02:38 PM
To: Excel-DNA
Subject: [ExcelDna] how to refresh when using exceldna rtd

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[Deepak Krishna]

Hi Govert, 

Thank you for your answer.

I was using ExcelAsyncUtil but trying RTD server to implement batching multiple requests into one request. 

Yes I was talking more from UI point of view, like if user wanted to refresh the formula from cell. 

Any event gets triggered in this scenario, so that Ill get notified and programatically fetch new data and update the cell

I though this would work Server.DisconnectData(Topic). Even after this the cell would not refresh. Am I not disposing the topic by calling the function?

[Govert van Drimmelen]

There's a sample of doing async batches here: https://github.com/Excel-DNA/Samples/blob/master/Misc/AsyncBatchSample.cs

Server.DisconnectData(Topic) is called by Excel when the RTD formula is cleared from the cell. You should certainly not call this yourself.

I think the normal way to implement RTD refresh functionality would be to add a button (and maybe a shortcut key) that internally causes your RTD server to get new data for the live topics, and then update the topics via Topic.UpdateData().

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Deepak Krishna [kr...@sentieo.com]

Sent: 11 March 2016 09:10 AM
To: Excel-DNA
Subject: [ExcelDna] Re: how to refresh when using exceldna rtd

--

[anonym...@gmail.com]

Hi,

When using ExcelAsyncUtil.Run or ExcelAsyncUtil.Observe my functions seem to become volatile. Is there any way of preventing this?

Ideally I'd want to use Excel's multithreaded calculation (which I know is not possible with the above 2 features) to call my non-volatile function which makes a call to a web service and then updates the single caller cell.

Where multiple cells have the same parameters they subscribe to the same request and each cell is then updated once the request is complete.

A cache (the key for each item of the cache will be based on the hashcode of the parameters passed into the function) will be used and will be checked first. If the data already exists then it will be returned immediately. 

Any thoughts?

[Govert van Drimmelen]

>> When using ExcelAsyncUtil.Run or ExcelAsyncUtil.Observe my functions seem to become volatile. 

This is not correct.

In Excel, a function that is volatile will recalculate every time you press F9 (or change some value on the sheet). The ExcelAsyncUtil.Run or ExcelAsyncUtil.Observe do not behave this way.

As you test, you can make the following three functions:

using System;

using System.Threading;

using ExcelDna.Integration;

namespace CSAsyncVolatile

{

    public static class TestFunctions

    {

        static Random _rand = new Random();

        [ExcelFunction(IsVolatile = false)]

        public static object randNonVolatile()

        {

            return _rand.NextDouble();

        }

        [ExcelFunction(IsVolatile = true)]

        public static object randVolatile()

        {

            return _rand.NextDouble();

        }

        [ExcelFunction]

        public static object randAsync()

        {

            return ExcelAsyncUtil.Run("randAsync", null, () =>

            {

                Thread.Sleep(2000);

                return _rand.NextDouble();

            });

        }

    }

}

No in a new Excel sheet, put the three functions:

A1:  =randNonVolatile() 

A2:  =randVolatile()

A3:  =randAsync()

Cell A3 will initially show #N/A and after two seconds will update to a new value.

After that, if you press F9, only cell A2 (with the volatile function) will recalculate.

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of anonym...@gmail.com [anonym...@gmail.com]
Sent: 12 March 2016 01:12 PM

To: Excel-DNA
Subject: [ExcelDna] Re: how to refresh when using exceldna rtd

--

[anonym...@gmail.com]

Thanks Govert!

Any opinion on using a custom implementation so that I can mark my function threadsafe and use Excel's multiple calculation threads?

On Thursday, March 10, 2016 at 12:38:25 PM UTC, Deepak Krishna wrote:

[Govert van Drimmelen]

Functions that call ExcelAsyncUtil.Run or ExcelAsyncUtil.Observe (or that call RTD in any other way) cannot be marked IsThreadSafe=true or participate in Excel's multi-threaded calculation. This is an Excel limitation. But it's not typically a problem, because the slow work in such a function can be done on a separate thread, or using a separate callback mechanism that your function sets up. The wrapper function (which must run on the main calculation thread) does not do much work - it just starts off the async calls, and when they're done, returns the results.

If you don't use these RTD-based mechanisms in your function, you can mark them as IsThreadSafe=true, and use Excel's multiple calculation threads for your web service calls.

Excel doesn't always make it easy, so a general mechanism to do lots of async web service work from Excel functions is still quite a challenge and a lot of work to set up. 

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of anonym...@gmail.com [anonym...@gmail.com]

Sent: 12 March 2016 03:54 PM

To: Excel-DNA
Subject: [ExcelDna] Re: how to refresh when using exceldna rtd

--