Excel DNA UDF always recalculating

Jan 21, 2012 at 10:17 PM
Salut Govert, I came across a strange behaviou with the UDF I created in my XLL file. My UDF are always recalculating despite no change in their parameters. Is there something wrong or is there a toggle somewhere that I don't know about? I have look in both the codeplex and the Google group with no success. To confirm this behaviour, I created, in VBA, the same function I have in DNA referencing to the same cells as its parameter. Upon hiting "F9", the DNA function recalculated itself but not the VBA version. I made sure that the DNA version was identified as "isvolatile=false". I have many processes in my Addin that are critical for speed and trying to reduce to the essential the amount of calculated functions. Can you shed some light on this issue? Thanks in advance for your time, always apreciated. Guy P.S.: Which of the codeplex or the Google group is the prefered site for such question?
Coordinator
Jan 21, 2012 at 10:35 PM

Hi Guy,

I prefer the Google group for support, but monitor the CodePlex discussions too, so this is fine.

If the function that is registered as "IsMacroType=true" has an argument marked"AllowReference=true", Excel will treat the function as volatile even though you'd expect it not to be. This is because of the way Macro functions can interact with the sheet, in ways the subvert the dependency tree, and hence Excel's calculation sequence.

I add some functions exploring the different combinations in a .dna file below. When the functions are called from Excel this first two are not volatile, all the others are. 

If this does not explain what you see, perhaps you can pose a small function that does exhibit the problem.

Regards,

Govert

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

    public class TestFunctions
    {
        public static Random _random = new Random();
    
            [ExcelFunction(Category="dynamic Tests",
                    IsVolatile=false,
                    IsMacroType=false)]
        public static double GetRandomDefault()
        {
            return _random.NextDouble();
        }
        
        [ExcelFunction(Category="Volatile Tests", 
                IsVolatile=false,
               IsMacroType=true)]
        public static double GetRandomMacroType(object ignored_arg)
        {
            return _random.NextDouble();
        }
        
        [ExcelFunction(Category="Volatile Tests", 
                       IsVolatile=true,
                       IsMacroType=false)]
        public static double GetRandomVolatile()
        {
            return _random.NextDouble();
        }
        
        [ExcelFunction(Category="Volatile Tests", 
                       IsVolatile=false,
                       IsMacroType=true)]
        public static double GetRandomSetVolatileMacroType()
        {
            XlCall.Excel(XlCall.xlfVolatile, true);
            return _random.NextDouble();
        }
        
        [ExcelFunction(Category="Volatile Tests", 
                IsVolatile=true,
               IsMacroType=true)]
        public static double GetRandomMacroTypeAllowReference([ExcelArgument(AllowReference=true)] object ignored_arg)
        {
            return _random.NextDouble();
        }
        

    }
]]>
</DnaLibrary>

Jan 22, 2012 at 9:37 PM
Salut Govert,

As expected, it worked. Thanks for a very useful tip and quick response.

On the same subject. Can you explain the difference between an argument defined as an object vs defined with the "AllowReference=true" (or direct me to a thread that does)? I was under the impression that the latter applied to argument of type range.

In the mean time, I will make some test to assess the difference.

Thanks again,

Guy
Envoyé de mon iPad

Le 2012-01-21 à 18:35, "govert" <notifications@codeplex.com> a écrit :

From: govert

Hi Guy,

I prefer the Google group for support, but monitor the CodePlex discussions too, so this is fine.

If the function that is registered as "IsMacroType=true" has an argument marked"AllowReference=true", Excel will treat the function as volatile even though you'd expect it not to be. This is because of the way Macro functions can interact with the sheet, in ways the subvert the dependency tree, and hence Excel's calculation sequence.

I add some functions exploring the different combinations in a .dna file below. When the functions are called from Excel this first two are not volatile, all the others are.

If this does not explain what you see, perhaps you can pose a small function that does exhibit the problem.

Regards,

Govert

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

    public class TestFunctions
    {
        public static Random _random = new Random();
    
            [ExcelFunction(Category="dynamic Tests",
                    IsVolatile=false,
                    IsMacroType=false)]
        public static double GetRandomDefault()
        {
            return _random.NextDouble();
        }
        
        [ExcelFunction(Category="Volatile Tests", 
                IsVolatile=false,
               IsMacroType=true)]
        public static double GetRandomMacroType(object ignored_arg)
        {
            return _random.NextDouble();
        }
        
        [ExcelFunction(Category="Volatile Tests", 
                       IsVolatile=true,
                       IsMacroType=false)]
        public static double GetRandomVolatile()
        {
            return _random.NextDouble();
        }
        
        [ExcelFunction(Category="Volatile Tests", 
                       IsVolatile=false,
                       IsMacroType=true)]
        public static double GetRandomSetVolatileMacroType()
        {
            XlCall.Excel(XlCall.xlfVolatile, true);
            return _random.NextDouble();
        }
        
        [ExcelFunction(Category="Volatile Tests", 
                IsVolatile=true,
               IsMacroType=true)]
        public static double GetRandomMacroTypeAllowReference([ExcelArgument(AllowReference=true)] object ignored_arg)
        {
            return _random.NextDouble();
        }
        

    }
]]>
</DnaLibrary>

Coordinator
Jan 22, 2012 at 9:48 PM

Hi Guy,

The "AllowReference=true" option only affects parameters of type object. It changes the registration type of the function to tell Excel to pass an ExcelDna.Integration.ExcelReference if the function is called with a sheet reference. Otherwise the function would always get the dereferenced value.

In the xlfRegister registration call, this is the difference between the P type (Q in Excel 2007/2010) and the R type (U in Excel 2007/2010) that you can read about here: http://msdn.microsoft.com/en-us/library/bb687900.aspx.

On the same page the note regarding volatile states: "By default, functions that take type R XLOPERs or type U XLOPER12s and that are registered as macro sheet equivalents (type #−see next section) are handled as volatile in Excel." Here "macro sheet equivalent" means an Excel-DNA function marked "IsMacroType=true". 

Regards,

Govert

Aug 2, 2013 at 7:00 PM
Hi Govert,

I've got a problem that could be demonstrated with one of the UDFs above. If I try to evaluate Excel function that contains more than one UDF, it returns an error. For example:
Application.Evaluate("=GetRandomDefault()+GetRandomDefault()")

However, if I leave just one UDF I get the correct answer even though I may use other Excel built-in function there, i.e.
.Evaluate("=GetRandomDefault()+1")