Excel Dna - Refresh all Data Source and Formula Calculation

Question

I have couple of formulas and data coming from database. I want to refresh all the formulas programmatically through ExcelDna. All these formulas are ExcelFunctions and I have put ExcelCommand name = "Refresh" on which i want to issue recalculate to excel sheet..

I use following but it refresh only native excel functions e.g. NOW(), SUM() etc. it does not invoke refresh on ExcelDna Functions ?

[ExcelCommand(MenuName="Refresh", MenuText="Refresh"   )]
        public static void GetPositionCommand()
        {
            XlCall.Excel(XlCall.xlcCalculateNow);
        }

Thanks in advance...

Sputter

Answer 1

xlcCalculateNow will only calculate formulae that Excel knows have to be recalculated. You can mark an Excel function as 'Volatile' for it to behave like Excel's NOW() function, which is recalculated every time. With Excel-DNA you can do this:

[ExcelFunction(IsVolatile=true)]
public static string MyVolatileNow()
{
    return DateTime.Now.ToString("HH:mm:ss.fff");
}

and compare with the default non-volatile case:

[ExcelFunction]
public static string MyNow()
{
    return DateTime.Now.ToString("HH:mm:ss.fff");
}

Another way to push data to Excel is to create an RTD server or use the new Reactive Extensions for Excel (RxExcel) support in the latest Excel-DNA check-ins. Some info here - http://exceldna.codeplex.com/wikipage?title=Reactive%20Extensions%20for%20Excel.

Bagatelle

Thanks; this kind of worked. But I prefer following; I make function NonVolatile and with Ctrl + Shift + F9 which refresh all ExcelFunction. If I make it volatile; it goes in endless loop of update upon hitting Ctrl + Shift + F9.. not sure whey... – Sputter

When a funciton in marked as Volatile it is refreshed every time user takes some action like column resize. Is it possible to programmaticaly refresh selected functions (only when a custom defined command is run)? – Multifarious

Answer 2

I have to hit Ctrl + Alt + F9 (at least in Excel 2013) to refresh the cells!

Badtempered

Answer 3

This works (at least in Excel 2013):

Excel.Application excelApp = ExcelDnaUtil.Application as Excel.Application;
excelApp.CalculateFull();

or

Excel.Application excelApp = ExcelDnaUtil.Application as Excel.Application;
excelApp.CalculateFullRebuild();

The first one recalculates all Workbooks (similar like you press Ctrl + Alt + F9), the second one is similar to reentering all formulas.

Multifarious

This works for me as well in Excel 2010, with IsVolatile=false UDFs – Hadlee

Answer 4

Try setting IsVolatile=false in the attribute for the ExcelDNA function:

[ExcelFunction(Description = "Subscribe to real time data.", IsVolatile = false, Category = UDF_CATEGORY)]
public static object XSub(        
    string param1)
{
    // Implementation here.
}

This simple change dramatically drops CPU usage. In one big spreadsheet I was working with, CPU usage dropped from 100% (i.e. continuously recalculating, and never catching up) down to 20%.

With IsVolatile=true, Excel will recalculate the output value of the function regularly, even if the inputs did not change.

With IsVolatile=false, Excel will only recalculate the output value of the function if the inputs change.

With IsVolatile=false, everything is an order of magnitude more efficient, as Excel can skip recalculating 90% of the spreadsheet most of the time.

Having said this, some functions might need IsVolatile=true, for example, a function that returned the current time.

Lots