Excel XLL AcceptAsArray = False causes the function to be volatile

Add-in Express™ Support Service
That's what is more important than anything else

Excel XLL AcceptAsArray = False causes the function to be volatile
 
nwein




Posts: 510
Joined: 2011-03-28
You probably thought you've heard the last of me and my volatility issues :)
I've just encountered this behaviour that when an XLL UDF has one of its parameters AcceptAsArray property set to False it causes the function to be volatile, i.e. gets called after each calculation.

This is only true when the parameter is an object and is passed through as a Range.
Once the parameter is set to have AcceptAsArray = False the parameter type becomes ADXExcelRef (with AcceptAsArray = True the parameter is the value of the cell pointing to)
and this is when it becomes volatile.

Here's a simple code that you can try to use to replicate it inside the UDF portion of an XLL module:
(both functions have their parameter set to have AcceptAsArray = False)

public static object Test1(string range) // Not volatile
{
     return range;      
}
// Usage: =Test1("hello")
// Outputs: hello

public static object Test2(object range) // Volatile
{
     return ((ADXExcelRef) range).GetValue();
}
// Usage: =Test2(A1)
// Outputs: value of A1


The output is what I expect it to be (which is good), but the fact that it becomes volatile is horrible.

Add-in Express 2010.6.4.3056 on Windows 7 64-bit, Office/Excel 2010 32-bit

(hey at least this time it has nothing to do with RTD :D )
Posted 08 Jul, 2011 14:44:32 Top
Andrei Smolin


Add-in Express team


Posts: 14671
Joined: 2006-05-11
Hi Nir,

This behavior is the default Excel behavior. Below is a citation from Steve Dalton's Financial Applications Using Excel Add-in Development in C/C++ (2nd edition):

The logic behind Excel treating these functions as volatile is that if you
want to calculate something based on the reference, i.e. the location of a cell, then you
must recalculate every time in case the location has changed but the value has stayed the
same.


Modifying Test2 as shown below makes it non-volatile:

public static object Test2(object range) // Volatile
{
    Module.CallWorksheetFunction((ADXExcelWorksheetFunction)237, new object[] { false });
    System.Diagnostics.Debug.WriteLine("!!! Test2");
    return ((ADXExcelRef)range).GetValue();
}


237 above stands for xlfVolatile. The passage describing the use of this function is accompanied with this statement:

However, there are reports that this can confuse Excel’s order-of-recalculation logic, so the advice would be to decide at the outset whether your functions need to be volatile or not, and stick with that.


Regards from Belarus (GMT+2),

Andrei Smolin
Add-in Express Team Leader
Posted 11 Jul, 2011 07:16:22 Top
nwein




Posts: 510
Joined: 2011-03-28
Wow, thanks. I don't think I would have ever known that have you not described it here.

I do have another resolution though it is not perfect either, by using Excel's precedents I can achieve the same as ADXExcelRef withuot it being volatile (when the parameter is a range), however the downfall is that precedents only work for active sheet so if a we're referring to a range not in the current sheet this doesn't work.

I'll try that 237 trick (how on earth does someone suppose to know that?!? :D) just to give it a try.
Posted 11 Jul, 2011 10:37:34 Top
Andrei Smolin


Add-in Express team


Posts: 14671
Joined: 2006-05-11
Nir,

:)

Please keep me notified about your results.

Regards from Belarus (GMT+2),

Andrei Smolin
Add-in Express Team Leader
Posted 11 Jul, 2011 10:44:42 Top
nwein




Posts: 510
Joined: 2011-03-28
The 237 works well, I don't see any issues with re-calculation order.
I'm gonna stick with it for now.
Thanks!
Posted 11 Jul, 2011 13:07:41 Top
Andrei Smolin


Add-in Express team


Posts: 14671
Joined: 2006-05-11
Thank you for letting me know!

Regards from Belarus (GMT+2),

Andrei Smolin
Add-in Express Team Leader
Posted 12 Jul, 2011 03:24:02 Top