CODEWITHSUNDEEP
c#excel-addinsexcel-dna
masaishi
masaishi

Reputation: 21

Excel-Dna Volatile Functions in Massive Updating Spreadsheet

I have created an Add-In using Excel-Dna and marked the exported functions as Volatile (IsVolatile=true) which gives me the desired behavior. Functions make use of simple queries to retrieve data from a SQL Server instance.

However, in a particular case of a spreadsheet with hundreds of cells linked to external sources, the recalculation usually freezes the Excel instance.

Although I would like to keep the Volatile feature, it would be useful if I could skip the function recalculation if, for instance, input parameters are the same and the last call was made in a tiny time interval. Not sure if this is advisable, but I am trying to workaround to avoid IsVolatile=False.

Upvotes: 0

Views: 906

Answers (1)

Govert
Govert

Reputation: 16907

You could keep a cache inside your add-in which stores the last values returned and a time last checked, using something like MemoryCache. You can also consider figuring out whether you can make the functions thread-safe. Volatile is generally a bad idea though - if the back-end data changes often you need a real-time feed - see the series of Excel-DNA RTD examples. That could help you get rid of volatile, but still update the data regularly (when changed).

Upvotes: 1

Related Questions