Marking a Function as Volatile

Dec 13, 2010 at 7:22 PM

I wanted to designate some functions as volatilie so they will recalculate each time the spreadsheet is recalculated, regardless whether their arguments have changed. According to Microsoft's documentation I should be able to this by inserting a call

Application.Volatile(true)

first thing in the function. However, Microsoft's example is in VB only and I am using C#. I tried this in my ExcelDNA using the following code inside my function:

Excel.Application app = (Excel.Application)ExcelDna.Integration.ExcelDnaUtil.Application;
app.Volatile(true);
return GetHfaData(itemType, itemID, CodeName);

but it does not seem to make any deifference in the recalculation behavior. Should this work with ExcelDNA, or is there another method to designate that a function is volatile?

Thanks

Peter

Coordinator
Dec 14, 2010 at 2:29 PM

Hi Peter,

There are two ways to do this in Excel-Dna. The first is to set IsVolatile=true in the ExcelFunction attribute. The second is to call xlfVolatile, which you can only do from functions marked as macro-sheet equivalent using IsMacroType=true.

Both ways are exhibited in the example below.

Regards,

Govert

 

<DnaLibrary Name="Volatile Tests" Language="CS">
<![CDATA[
    using System;
    using ExcelDna.Integration;

    public class TestFunctions
    {
        public static Random _random = new Random();
    
        [ExcelFunction(IsVolatile=true)]
        public static double GetRandomDouble()
        {
            return _random.NextDouble();
        }
        
        [ExcelFunction(IsMacroType=true)]
        public static int GetRandomInteger()
        {
            XlCall.Excel(XlCall.xlfVolatile, true);
            return _random.Next();
        }
    }
]]>
</DnaLibrary>