Chart depending on UDF not updating

May 26, 2011 at 5:08 PM

Hello again,

I've got a working UDF which takes a SPARQL query string, executes the query against a SPARQL endpoint and returns an array (that is an array formula).

Note: SPARQL is a query language comparable to SQL, but for RDF (semantic web) data.

This works great, and thanks to ExcelDNA I was able to transform my previous VBA version into C#/XLL in no time.

However, there is one problem that bugs me (did already with the VBA version):

When data has changed in database, I need to press CTRL-ALT-F9 to update the date in Excel ... this is expected, and I want it like this (not volatile, since then queries will be executed all the time).

I works, in that the query is reexecuted, and data changes in Excel.

However, when I have a chart depending on that data, the chart isn't updated. To force an update of chart, I need to "touch" the formula input (that is press F2 and return).

I've verified that the problem does not relate to array formulas. Consider the following UDF:

    public class MyFunctions
    {
        private static Random rnd = new Random();

        public static int GetRandomInt(int min, int max)
        {           
            return rnd.Next(min, max);
        }

and an Excel sheet:

A1 = 2
A2 = 8
B1 =GetRandomInt(A1;A2)
B2 =GetRandomInt(A1;A2)

and a Pie chart on B1 and B2.

When hitting CTRL-ALT-F9, B1/B2 changes, but not the chart.

When I mark the function as volatile, both the data and the chart will update on each F9 hit.

However, I don't want to mark it as volatile.

Is there something I could try?

Thanks again,

Tobias

Coordinator
May 28, 2011 at 8:00 AM

Hi Tobias,

To me it seems to me the function should be volatile if another call with the same parameters can lead to a different answer (like your GetRandomInt() function). You could add a cache that only checks back to the database for new data from time to time, so that immediate recalculations remain fast. The only other way to approach this is to use the RTD feature, which will allow you to tell Excel when the function should be updated. I'm not sure if an RTD update would also update in the chart, but I imagine so.

Otherwise, since you say the same problem was already present in VBA, I suggest you try the Excel for Developers forum: http://social.msdn.microsoft.com/Forums/en-US/exceldev/threads. Someone there might know of another trick.

Regards,

Govert

May 28, 2011 at 1:03 PM

Hi Govert,

thank you for your hints .. I've posted the question to the list you suggested (http://social.msdn.microsoft.com/Forums/en-US/exceldev/thread/56ad642e-f167-489f-bd4a-68ec3b452110) .. see if there is something I can do.

If not, I'll consider your caching idea .. maybe adding a button which clears the cache so that the user has control over when to really re-query the database.

The RTD stuff is new to me, but seems very powerful. I need to check that out ..

Thanks again,

Tobias

Von: govert [email removed]
Gesendet: Samstag, 28. Mai 2011 10:01
An: Tobias Oberstein
Betreff: Re: Chart depending on UDF not updating [exceldna:259129]

From: govert

Hi Tobias,

To me it seems to me the function should be volatile if another call with the same parameters can lead to a different answer (like your GetRandomInt() function). You could add a cache that only checks back to the database for new data from time to time, so that immediate recalculations remain fast. The only other way to approach this is to use the RTD feature, which will allow you to tell Excel when the function should be updated. I'm not sure if an RTD update would also update in the chart, but I imagine so.

Otherwise, since you say the same problem was already present in VBA, I suggest you try the Excel for Developers forum: http://social.msdn.microsoft.com/Forums/en-US/exceldev/threads. Someone there might know of another trick.

Regards,

Govert