Possible to let a custom formula return a list of values?

Daniel Wosch

Guest

I've implemented an own excel function which returns a list of values.
Code behind the result object holds the list of double values.

When the values are returned to excel the cell holding the formula gets the excel error "#VALUE!".
Is there a way to insert the whole list of values to the cells below the cell which is holding the formula?

My function looks like following:

 
public static object ExecuteMyFunction(object values, object tagName)
            {
                object result = AddinExpress.MSO.ADXxlCVError.xlErrValue;

                try
                {
                    // Gets the address of the cell of which the formula has been executed from
                    var callerAddress = getCallerAddress();

                    if (values is Array)
                    {
                        // Check if a tag name has been provided by the caller
                        if (tagName == null && string.IsNullOrEmpty(Convert.ToString(tagName)) && string.IsNullOrWhiteSpace(Convert.ToString(tagName)))
                        {
                            // Tag name not provided
                            result = MyExcel.Functions.Denoise(Converter.ConvertTo<double>(values as Array), 
                                                                            MyExcel.Functions.Sample.a, 
                                                                            MyExcel.Functions.Sample.a.Order.ORDER_1_1,
                                                                            MyExcel.Functions.Model.Threshold.Scale_1);
                        }
                        else
                        {
                            // Tag name provided
                            result = MyExcel.Functions.Denoise(Converter.ConvertTo<double>(values as Array), 
                                                                            MyExcel.Functions.Sample.a, 
                                                                            MyExcel.Functions.Sample.a.Order.ORDER_1_1,
                                                                            MyExcel.Functions.Model.Threshold.Scale_1, 
                                                                            Convert.ToString(tagName));
                        }
                    }
                    else
                    {
                        result = "Not enough values provided!";
                    }
                }
                catch (InvalidCastException)
                {
                    if (Module.IsInFunctionWizard)
                    {
                        result = "The parameter must be numeric!";
                    }
                    else
                    {
                        result = AddinExpress.MSO.ADXExcelError.xlErrorNum;
                    }
                }
                catch (Exception)
                {
                    result = AddinExpress.MSO.ADXxlCVError.xlErrValue;
                }

                return result;
            }

I've already tried the following code:


                        var valueList = (List<double>)result;
                        Microsoft.Office.Interop.Excel.Range destinationCell = null;

                        // Gets the address of the cell of which the formula has been executed from
                        var callerAddress = getCallerAddress();
                        foreach (double value in valueList)
                        {
                            destinationCell = Module.ExcelApp.get_Range(callerAddress.Address);
                            destinationCell.Value2 = value;
                            callerAddress.Row++;
                        }

but it always throws an exception when i try to assign the value to the cell:


destinationCell.Value2 = value;
or
destinationCell.Value = value;

Andrei Smolin

Add-in Express team

Posts: 18821
Joined: 2006-05-11

Hello Daniel,

Please check section Can an Excel UDF modify multiple cells at https://www.add-in-express.com/docs/net-excel-udf-tips.php#modify-multiple-cells.

Andrei Smolin
Add-in Express Team Leader

Daniel Wosch

Guest

Hello Daniel,

Please check section Can an Excel UDF modify multiple cells at https://www.add-in-express.com/docs/net-excel-udf-tips.php#modify-multiple-cells.

Andrei Smolin
Add-in Express Team Leader

Hello Andrej,
thanks for your fast reply.

As i understand it is not "out of the house" possible to assign different values to a range of excel cells?
Further i don't know where i can find the

ADXExcelRef.SetValue()

method.

In my approach i am using the

Microsoft.Office.Interop.Excel.Range

object to get a reference to a specific cell by using

Module.ExcelApp.get_Range(formulaCell.Address)

to get the specific range object.

Is there maybe an code example to show how to use your linked approach?

Andrei Smolin

Add-in Express team

Posts: 18821
Joined: 2006-05-11

We don't recommend using ADXExcelRef.SetValue(); this approach may produce faults and exceptions that cannot be solved programmatically.

Writing to a cell other than the caller cell in the the context of an XLL UDF call is not possible. To bypass this restriction, you can make Excel switch from this context. This can be achieved if you have a combination of a COM add-in + XLL add-in. In this case, a UDF can prepare the values and other data, then [in this or that way] call the ADXAddinModule.SendMessage() method passing it a unique message id (an integer greater than 1024) and then return a result value to Excel [which puts the value to the caller cell]. When the UDF call ends, Excel switches from the XLL call context to the COM add-in context. Then the ADXAddinModule.OnSendMessage event occurs, and if it was caused by your message (check e.Message), you retrieve the values and put them to the required cells.

An example of using ADXAddinModule.SendMessage() and ADXAddinModule.OnSendMessage is given in section Wait a Little at https://www.add-in-express.com/docs/net-office-tips.php#wait-a-little.

Andrei Smolin
Add-in Express Team Leader

Daniel Wosch

Guest

Hello Andrej and thanks for your reply.
Seems like the suggested solution approach can cause other circumstances.
For Information: We have to support several Excel versions and i guess that this solution will cause not predictable side effects. Is there really no "out of the box" way to return an array or list of double values out of an UDF? Something like a matrix function like in excel?

nwein

Posts: 577
Joined: 2011-03-28

As Andrei has mentioned before - your best bet would be to call use array formulas. If you don't trust your users to select the necessary size of output, you can have it generated from a task pane, so the user only clicks a button (and passes parameters or whatever, per your requirements) and your code outputs the values as an array formula.
Granted, array formula is not the "nicest" thing to handle (can't modify any cell within the array), but that will get you what you want without compromising Excel.

Andrei Smolin

Add-in Express team

Posts: 18821
Joined: 2006-05-11

Hello Daniel,

This approach can be used with Excel 2000+.

If it is suitable for the user to enter an array formula, you can surely support this.

Andrei Smolin
Add-in Express Team Leader

Daniel Wosch

Guest

As Andrei has mentioned before - your best bet would be to call use array formulas. If you don't trust your users to select the necessary size of output, you can have it generated from a task pane, so the user only clicks a button (and passes parameters or whatever, per your requirements) and your code outputs the values as an array formula.
Granted, array formula is not the "nicest" thing to handle (can't modify any cell within the array), but that will get you what you want without compromising Excel.

Sure - I've got a task pane which expects several parameters for the formula.
Is there a way to 'trigger' an array / matrix formula via the task pane?
Setting the formula in curly brackets, for example: {=temp.MyFormula(a,b,c)}, seems not to trigger an array / matrix formula if its set code behind. Do i miss something

Andrei Smolin

Add-in Express team

Posts: 18821
Joined: 2006-05-11

Hello Daniel,

Please see https://msdn.microsoft.com/en-us/library/office/ff837104.aspx.

Andrei Smolin
Add-in Express Team Leader