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

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.