XLL UDF is called prematurely when parameter is range containing formulas

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

XLL UDF is called prematurely when parameter is range containing formulas
Works when AcceptAsArray=True, fails when AcceptAsArray=False 
Posts 1 - 10 of 14
First | Prev. | 1 2 | Next | Last 
David Jones


Guest


Hi there

I've encountered a problem when creating an XLL UDF with a range parameter where AcceptAsArray=False. If the referenced range contains formulas which create dependencies within the range, the UDF is called before the range has fully recalculated so produces an incorrect result. It doesn't get called again once the array has fully recalculated. This does not occur when AcceptAsArray=True.

I've created a simple test project that demonstrates this (tested in Excel 2007 and 2010). It contains two UDFs which each calculate the SUM of a range of cells: 


            // AcceptAsArray==True
            public static double ADXSumArray(object range)
            {
                object[,] valueArray = range as object[,];

                if (valueArray == null)
                    return 0;

                int numRows = valueArray.GetLength(0);
                int numCols = valueArray.GetLength(1);
                double runningTotal = 0;

                for (int rowIndex = 0; rowIndex < numRows; rowIndex++)
                {
                    for (int colIndex = 0; colIndex < numCols; colIndex++)
                    {
                        runningTotal += Convert.ToDouble(valueArray[rowIndex, colIndex]);
                    }
                }

                return runningTotal;
            }

            // AcceptAsArray==False
            public static double ADXSumObject(object range)
            {
                // Andrei's trick to make function non-volatile
                // Module.CallWorksheetFunction((ADXExcelWorksheetFunction)237, new object[] { false });

                AddinExpress.MSO.ADXExcelRef pageContents = (AddinExpress.MSO.ADXExcelRef)range;
                object rangeObject = pageContents.GetValue();
                object[,] valueArray = rangeObject as object[,];
                if (valueArray == null && rangeObject != null)
                    valueArray = new object[,] { { rangeObject } };

                int numRows = valueArray.GetLength(0);
                int numCols = valueArray.GetLength(1);
                double runningTotal = 0;

                for (int rowIndex = 0; rowIndex < numRows; rowIndex++)
                {
                    for (int colIndex = 0; colIndex < numCols; colIndex++)
                    {
                        runningTotal += Convert.ToDouble(valueArray[rowIndex, colIndex]);
                    }
                }

                return runningTotal;
            }

        }


I'm testing these functions on a range that looks like this:
C1: 1
C2: =C1*2
C3: =C1*3
C4: =C2+C3

When I change the value in C1, ADXSumArray returns the correct result (same as Excel SUM function) but ADXSumObject returns an incorrect value (the sum of the partially recalculated range).

The problem does not seem to occur in a non-ADX UDF.

I'll send the test project to the support email address.

Thanks very much

David
Posted 25 May, 2013 07:57:01 Top
Andrei Smolin


Add-in Express team


Posts: 18817
 Joined: 2006-05-11
Hello David,

Thank you for sending use the project and the sample workbook. I've reproduced this issue.

I believe what you see is a result of how Excel tries to build the calculation tree if the range is passed indirectly. Indirectly, since "AcceptAsArray=false" actually points to a rectangle of cells, not to the cells' values; the values are retrieved when Excel decides to cll the UDF.

From what I see in Excel 2013, the calculation tree is this: 1) C1; 2) C2 & C3; 3) ={UDF}(C1:C4); 4) C4. Note that in your sample, two cells depend on C1 and the UDF refers to a range of cells which includes C1; if you use =ADXSumArray(C1:C4), this requires Excel to recalculate all cells, if you use =ADXSumObject(C1:C4), Excel's decision is different. It makes a guess and the guess is wrong.

Now, let's try the idea from Financial Applications Using Excel Add-in Development in C-C++: you can use a trigger argument. The trigger argument isn't used in the UDF, it is used only to help Excel build the calculation tree. I've modified the declaration of ADXSumObject as follows: 

public static double ADXSumObject(object range, object trigger)


In the workbook, I call ADXSumObject as follows:

=ADXSumObject(C1:C4, C4)

If the trigger parameter is marked as AcceptAsArray=true, the formula returns a correct result; otherwise, it returns the same result as if the parameter is omitted.

What do you think?


Andrei Smolin
Add-in Express Team Leader
Posted 27 May, 2013 07:18:50 Top
David Jones


Guest


Hi Andrei

Thanks for your reply. Adding a trigger parameter does seem to solve the problem, but it's a workaround rather than a neat solution!

I've implemented the same function as an automation add-in and it doesn't show the same behaviour with the same range i.e. it runs once after the range has fully-recalculated. Why does this work where ADXExcelRef does not? 


       public object AutoSumObject(Excel.Range TargetRange)
       {
            double result = 0;
            object[,] values = TargetRange.get_Value() as object[,];

            foreach (object obj in values)
            {
                result += Convert.ToDouble(obj);
            }

            return result;
        }


I've based this on an example from the MS site - I can send you the full project if necessary.

Thanks

David
Posted 28 May, 2013 05:05:08 Top
Andrei Smolin


Add-in Express team


Posts: 18817
 Joined: 2006-05-11
Hello David,

I'm afraid there's no solution as this belongs to how Excel builds the calculation tree.

But please do send the full project.


Andrei Smolin
Add-in Express Team Leader
Posted 28 May, 2013 05:51:03 Top
David Jones


Guest


Hi Andrei

I've just emailed the zipped project. When you run it you should see that the AutoSumObject function is called once, when the range has finished updating.

I still don't really understand why my ADXSumObject function is not working. If Excel makes a mistake when working out the dependency tree, I would expect ADXSumObject to get called twice - once incorrectly, and once at the end of the range recalculation, because the range is dirtied again after C4 recalcs:

i.e. the calculation tree is this: 1) C1; 2) C2 & C3; 3) ={UDF}(C1:C4); 4) C4 5) ={UDF}(C1:C4);

Thanks

David
Posted 28 May, 2013 06:18:10 Top
Andrei Smolin


Add-in Express team


Posts: 18817
 Joined: 2006-05-11
David,

Thank you for sending me the project. Please note that AutoSumObject in that project is an Excel Automation add-in function; since its parameter is defined as Excel.Range, Excel calculates the range before passing it to your function. This is a complete equivalent of ADXSumArray in your sample project.

At the moment, I suggest that you use AcceptAsArray=true.

We've decided to study this deeper. We will create a sample based on Excel SDK and compare the results with Add-in Express based XLL add-in. We will inform you about our results. Still, I'm not sure what the timeline for creating the example will be.


Andrei Smolin
Add-in Express Team Leader
Posted 28 May, 2013 07:58:27 Top
David Jones


Guest


Thanks for looking into this a bit further - please keep me posted!

I don't understand your point about the automation add-in being the same as ADXSumArray. The Excel.Range parameter to AutoSumObject is not just an array of values - it includes all formatting info etc. Surely this makes it closer to ADXSumObject?

Thanks!

David
Posted 28 May, 2013 08:34:44 Top
Andrei Smolin


Add-in Express team


Posts: 18817
 Joined: 2006-05-11
Not at all.

- in ADXSumObject, you use AcceptAsArray=False, and Excel provides the cells' coordinates, that you use to retrive data in the code of the method; here, Excel may not know about your intensions;
- in ADXSumArray, you use AcceptAsArray=True thus asking Excel to provide data (not coordinates); here, Excel knows that data must be calculated;
- in AutoSumObject, you ask for data, too; here, Excel knows that data must be calculated as well.

That is, there are two explanations possible:
1) this is a bug in Add-in Express
2) this is how Excel behaves

To find this out, we will create a sample project using Excel SDK. I'll let you know about results.


Andrei Smolin
Add-in Express Team Leader
Posted 28 May, 2013 09:05:26 Top
David Jones


Guest


Hi there - have you managed to find an answer this question yet?

Thanks

David
Posted 07 Jun, 2013 05:36:33 Top
Sergey Grischenko


Add-in Express team


Posts: 7233
 Joined: 2004-07-05
Hi David,

Please set the 'IsMacro' property to false for the 'ADXSumObject' function in the 'ADXExcelFunctionCategory' component. It should fix the issue.
Posted 10 Jun, 2013 06:27:32 Top

Posts 1 - 10 of 14
First | Prev. | 1 2 | Next | Last