Resolved pass an optional 2 dimensional params array argument?

[justhumm]

I'm trying to throw together an Excel add-in with ExcelDNA. I think I set up the ExcelDNA.registration "correctly". And I'd like to make a UDF that can handle one or more inputs/arguments as static values, single cell references, or ranges of cells. Ultimately, what I would like to get to is this:

=C_RangeSum(A1:D1,A2:D2) = (1+3+5+7) + (2+4+6+8) = 36

I tried using "params double[,]" to input multiple 2D "arrays" of excel ranges...

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?

// ==================== START of Function ====================
// Description for IntelliSense Tool Tip
[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 --------------------

 

[Skydiver]

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

No. Not with current version of the language. Why not just have an overloaded function instead?

 

[justhumm]

Thanks for the response, @Skydiver . I did some reading on an ExcelDNA message board and it sounds like the "best" (only) way to use multiple arrays of varying sizes or data types is to hard code them in as object data types (an ExcelDNA type, I think) and do some background processing. So I basically got what I wanted going that route.

Now I'm just wondering if there's an expedient to process a bunch of hard coded arguments...?

he best thing I could think of was to hard code them all into a list and then loop through that...suggestions?

Thanks!

// 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;
}