How to handle Excel array formula in ExcelDNA XLL UDF

Question

I'm really struggling to understand how to allow for the possibility of my UDF being entered as an array formula. I've looked through the various posts for an answer but I think there must be a gap in my understanding.

Using the ExcelDNA tutorial double MultiplyThem(double x, double y) as an example to demonstrate.

If I select a range of cells and enter {=MultiplyThem({1;2;3;4},5)} as an array formula I would expect to see the selected column range filled with

5 10 15 20

however, what I get is all 5s. Also, if my target range is greater then the number of available values, I would expect to see #N/A values, but again I just see the value 5.

How, do I handle array formulae? Do I need UDF overload that returns a double[,], or is there some in-built Excel functionality that will repeatedly call my UDF with the appropriate array values.

Have you read through this? http://www.cpearson.com/excel/returningarraysfromvba.aspx or this previous question: http://stackoverflow.com/questions/1815980/excel-vba-function-returning-an-array — a-burge, Oct 24 '16 at 08:44

score 3 · Accepted Answer · answered Oct 24 '16 at 13:14

Excel UDFs can take arrays as input, and return arrays as result, but this has to be done explicitly. There no support for automatically extending a scalar function to work over arrays.

If you change the signature of your MultiplyThem function to be

public static object MuliplyThem(object x, object y) {…}

your function will receive the full array when called as {=MultiplyThem({1,2,3,4},5)}.

You then need to add a type check in the function to make sure you handle the different options correctly. (When you declare the parameter as ‘double’ Excel will try to convert the input, and return #VALUE if incompatible. Here you have to deal with the type checking and conversion.)

An exhaustive example of all the values you can get for an ‘object’ parameter looks like this:

[ExcelFunction(Description="Describes the value passed to the function.")]
public static string Describe(object arg)
{
    if (arg is double)
        return "Double: " + (double)arg;
    else if (arg is string)
        return "String: " + (string)arg;
    else if (arg is bool)
        return "Boolean: " + (bool)arg;
    else if (arg is ExcelError)
        return "ExcelError: " + arg.ToString();
    else if (arg is object[,])
        // The object array returned here may contain a mixture of different types,
        // reflecting the different cell contents.
        return string.Format("Array[{0},{1}]", ((object[,])arg).GetLength(0), ((object[,])arg).GetLength(1));
    else if (arg is ExcelMissing)
        return "<<Missing>>"; // Would have been System.Reflection.Missing in previous versions of ExcelDna
    else if (arg is ExcelEmpty)
        return "<<Empty>>"; // Would have been null
    else
        return "!? Unheard Of ?!";
}

In your case you’d handle object[,] arrays in a special way, and return a double[,] array or object[,] array when appropriate. It the inputs are not of types you can process, you can return an error, most likely ExcelError.ExcelErrorValue.

How to handle Excel array formula in ExcelDNA XLL UDF

1 Answers1