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
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?