How to convert Object from UDF parameter to Excel Range in Excel-DNA?

Question

I am developing a simple Addin using Excel-DNA. I have written a below function, but I am finding difficulties in converting it to a Range object. Tried googling and not able to figure out. Can someone please help me

[ExcelFunction(Description = "Excel Range")]
public static string Concat2([ExcelArgument(AllowReference = true)] object rng)
{
    try
    {
       // Assuming i am calling this from Excel cell A5 as =Concat2(A1:A2)
        var app = (Excel.Application)ExcelDnaUtil.Application;
        var r = app.Range[rng, Type.Missing];

        return r.Cells[1,1] + r.Cells[2,2]
    }

    catch (Exception e)
    {
        return "Error";
    }
}

Can you explain why you need to convert to a Range? Your example just uses the values, which you get as an `object[,]` through a call to `ExcelReference.GetValue()`. — Govert, Aug 09 '18 at 10:15
If you don't mark your function as `AllowReference=true` you will get an `object[,]` array with all the values from the input range. Note that it will be a 0-based array. Use `rng.GetLength(0 or 1)` to get the number of rows or columns. — Govert, Aug 09 '18 at 14:12
Thanks Govert. Can you please give an example for this, i am looking to iterate through cell values — Sam K, Aug 09 '18 at 14:50

score 1 · Answer 1 · answered Aug 10 '18 at 07:37

You should rather get the values directly from the input parameter, without getting the Range COM object. It's also much more efficient doing it that way.

Your simple function might then look like this:

    public static object Concat2(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;
    }

Typically you'd want to check the type of the value object, and do something different based on that. The object[,] array passed from Excel-DNA could have items of the following types:

double
string
bool
ExcelDna.Integration.ExcelError
ExcelDna.Integration.ExcelEmpty
ExcelDna.Integration.ExcelMissing (if the function is called with no parameter, as =Concat2()).

If you change the signature to have a single parameter of type object (instead of object[,]), like this:

    public static object Concat2(object value)

then, depending on how the function is called, you might get one of the above types as the value or you might get an object[,] array as the value, so your type checks would look a bit different before you do the iteration.

How to deal with `object[,]` arrays containing `int`'s that I want to get back as `int`s ? (I mean without getting back a `double` that one "converts" to `int` etc.) — Olórin, Jul 18 '19 at 07:56
Excel represents all numbers as doubles. If you want to interpret the numbers as it's, the conversion must happen in the add-in somewhere. — Govert, Jul 19 '19 at 08:31

score 0 · Answer 2 · answered Mar 24 '20 at 14:15

In my F# addin I have a function that does that (I use this function mainly to extract the displayed values of dates):

[<ExcelFunction(Description="Returns what is currently displayed as text.", IsMacroType=true)>]
let DISPLAYEDTEXT ([<ExcelArgument(Description="Cell", AllowReference=true)>] rng : obj) =
    app().get_Range(XlCall.Excel(XlCall.xlfReftext, rng, true)).Text

where app is:

let app()= ExcelDnaUtil.Application :?> Excel.Application

score 0 · Answer 3 · answered Jan 14 '21 at 10:40

How about this?

[ExcelFunction(IsMacroType = true)]
public static double GetBackColor([ExcelArgument(AllowReference=true)] object cell)
{
    ExcelReference rng = (ExcelReference)cell;
    Excel.Range refrng = ReferenceToRange(rng);
    return refrng.Interior.Color;
}

and this is the helper function

private static Excel.Range ReferenceToRange(ExcelReference xlRef)
{    
    Excel.Application app = (Excel.Application)ExcelDnaUtil.Application;
    string strAddress = XlCall.Excel(XlCall.xlfReftext, xlRef, true).ToString();
    return app.Range[strAddress];
}

How to convert Object from UDF parameter to Excel Range in Excel-DNA?

3 Answers3