Pass range as a parameter from Excel workbook function to Excel-DNA

[thiagogp...@gmail.com]

I tried to find the answer to the following question on the group, and found related questions, but I'm not able to understand it yet.

What I want to know is, is it possible to pass a range when calling the function in an Excel Workbook? For example:

=MyFunction(A2:A10)

or

=MyFunction(B5:B32)

In some examples I've seen here, people pass the starting row, ending row, starting column and ending column as parameters (example), but that would be bad for what I'm trying to accomplish.

Is there any documentation I can read to learn to do what I want? Or is it impossible for now?

Thank you

[Pankaj Sharma]

Hi,, u mean like this...

Function myfunction(range As range) As Long

    myfunction = Application.WorksheetFunction.Sum(range)

End Function

Pankaj Sharma                                                  

MIS Analyst

Greater Kailash-1, New Delhi

Email   pankaj...@gmail.com
Skype   pankaj.sharma_pj

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[Govert van Drimmelen]

Have a look here: http://exceldna.codeplex.com/wikipage?title=Range%20Parameters

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Pankaj Sharma [pankaj...@gmail.com]
Sent: 25 December 2015 07:31 AM
To: exce...@googlegroups.com
Subject: Re: [ExcelDna] Pass range as a parameter from Excel workbook function to Excel-DNA

[John Alexiou]

Except this does not work. An ExcelFunction argument cannot be of Range type. I get an " unsupported signature" when the XLL loads.

You could accept a string, and use 

public static int MyFunction(string reference) 
{

    Range range = xlApp.Range[reference];

}

But then you have to manually edit the string when things move around. Not the best solution but would

=MyFunction("A2:H21")

work as intended.

[Govert van Drimmelen]

If you declare the parameter as an object[,] array, you can pass ranges in and your function will get an array with the values.

public static object Concat(object[,] values)
{
    string result = "";
    int rows = values.GetLength(0);
    int cols = values.GetLength(1);
    for (int i = 0; i < rows; i++)
    {
        for (int j = 0; j < cols; j++)
        {
            object value = values[i, j];
            result += value.ToString();
        }
    }
    return result;
}

[John Alexiou]

I figured it out. The argument of the function must be of object type and the attribute set to AllowReference

public static int MyFunction([ExcelArgument(AllowReference = true)] object cell)

Then check that you actually have a reference

var xlApp = ExcelDnaUtil.Application as Application;
if (cell is ExcelReference reference)
{
    Range target = ToRange(reference);

    // ...
}
return 0;

The last step it to convert the ExcelReference to a Range object. See my post here on how to do this with a custom ToRange() utility function.