UDF optional array (range) arguments

[Andrew DeBear]

I'd like to make a UDF that can handle either a static value, a cell reference, or a range of cells.

I think I set up the ExcelDNA.registration "correctly". And I created/copied two simple functions to test it out. The params statement seems to be working for either a group single cell ranges or a single range of cells.

Any suggestions for how to get something like this to work? Thanks!

=C_RangeSum(A1,B2) = 1 + 4 = 5

// ==================== START of Function ====================

[ExcelFunction(Description = "Test function...first test for params (varying number of arguments in C#). Adds together all arguments.")]

public static double C_MySum(params double[] values)

    {

     return values.Sum();

}

// -------------------- END of Function --------------------

 // ==================== START of Function ====================

// Description for IntelliSense Tool Tip

[ExcelFunction(Description = "Test function...test to Add together all arguments in range.")]

public static double C_RangeSum(

[ExcelArgument(AllowReference = true)]

double[,] values) // params object[,] values)

{

     int rows = values.GetLength(0);

     int cols = values.GetLength(1);

     double value = 0;

     for (int i = 0; i < rows; i++)

     {

         for(int j = 0; j < cols; j++)

         {

             value = value + values[i, j];

         }

     }

     return value;

} // -------------------- END of Function --------------------

[Andrew DeBear]

To expand or clarify this a little, when I tried to input "params double[,]" so I could input a 2x2 (A1:B2, say) excel range...

          public static double C_RangeSum(params double[,] values)

But that gives a Compiler Error CS0225 (The params parameter must be a single dimensional array).

Is there a way to pass an optional 2 dimensional params array argument?

[Govert van Drimmelen]

Hi Andrew,

What you need to register is a function that looks like this:

public static double C_RangeSum(object val1, object val2, object val3, object val4, object val5) { }

There is a limit to the number of parameters here, but 125 parameters is safe, and 250 or so is around at the maximum limit.

If you have too many (this depends on a string passed to Excel by Excel-DNA) the registration will fail.

When the function is called, each argument value will then be one of the possible types:

* double

* string

* bool

* ExcelMissing

* ExcelEmpty

* ExcelError.ExcelErrorXXXX

* object[,] with a combination of the above.

You have to check the type of the argument and decide how to process further.

For your good case (the cases you are expecting according to your discussion), you'll either get a double, or an ExcelMissing value (if the formula does not have an argument in that position) or an object[,] array that you can process the contents of.

You have to decide what your function should do with ExcelEmpty input values (which come from empty cells), strings, error values etc.

In practice you'd make some helpers to deal with the type checking, or structure the code as you like.

If you have many such functions, you might want to generate the code as source or at runtime using lambda expressions, and register using ExcelIntegration.RegisterDelegates or RegisterLambdaExpressions. 

The ExcelDna Registration helper library provides support for simple "params" use cases, by doing exactly this. But it's not quite as clever as you want, allowing you to have both the array input values and the multiple optional parameters.

I hope that give you some ideas to start with.

-Govert

[Andrew DeBear]

OK. After cleaning up the error in my other thread from today, I was able to basically achieve what I wanted...

Multiple optional arguments that could accept either single cell or multi-cell ranges.

Now, I'm just wondering if there is a more expedient way to process all of the hard-coded arguments?

...the best option that came to mind is just just manually insert the arguments into a list and then loop through the list...

Thanks!

// ==================== START of Function ====================

// Description for IntelliSense Tool Tip

[ExcelFunction(Description = "Test function...test to Add together all arguments in defined ranges.")]

public static double C_SumRanges

([ExcelArgument(AllowReference = false)]   // Don't use "AllowReference = true" for object arguments

 object Range1, object Range2, object Range3, object Range4, object Range5

)

{

    double dblSum = 0;

    // Create List of function arguments and Loop through each

    List<object> ArgList = new List<object> {Range1,Range2,Range3,Range4,Range5};

    foreach (var i in ArgList)

    {

        // see "ArgOptional" helper class for more info

        double dblArg = ArgOptional.GetDbl(i);

        dblSum = dblSum + dblArg;

    }

    return dblSum;

} // -------------------- END of Function --------------------