Finding A Range Name

[Nemo]

In working and looking through ExcelDNA it is very impressive.
However I do have a question in relation to ExcelDNA and named
ranges. We have a c# function with signature of

static public object[,] MyUDF(object MyMatrix )

the user calls this from excel using a named range ie MyUDF("MyData")

in which "MyData" is defined as a named range in Excel.

When our UDF is called, we recieve the data (as a 1D or 2D matrix
depending on the named range).

The question is - we need to figure out the "name" of the named range
being passed in, how??? After the data is recieved we need a way to
figure out it is named range "MyData" that has been passed in as
opposed to some other named range. Is there a way to accomplish this
via the Application object in ExcelDNA or by changing our UDF
signature.

We have exactly the same scenario using an automation add-in, except
the automation add recives an Excel Range com object reference for the
named range, which contains not only the data but the "name" of the
range. We converted the automation add-in over to use ExcelDNA, but
could not figure out how to get the range name for the generic object
matrix we recieved.

Any help is appreciated,
TIA

[Govert]

Hi Nemo,

You need to take some care even from the VBA / Automation side with
the information that actually gets passed in from Excel's function
call. Say you have a function Function MyFunction(r as Range). When
Excel calls your function, you might receive a range object. But there
is no information visible from inside your function to know whether
the function is called with the range using the address or using a
name the refers to the range. If the name "MyName" refers to =Sheet1!$A
$1:$B$2 then =MyFunction(Sheet1!$A$1:$B$2) and =Myfunction(MyName)
will get exactly the same Range object. The Range.Name.Name property
you use gives the alphabetically first name that has the range as its
value. If you have "AnotherName" pointing to the same range and try
=Myfunction(MyName) you'll find that r.Name.Name returns
"AnotherName". The closest you can get to knowing how you were called
is to examine the formula of the caller. So it seems you are climbing
outside the Excel model a bit.

Having said all this, you can get the same behaviour in ExcelDna. You
can get a range into the ExcelDna function by tagging the object
argument with [ExcelArgument(AllowReference=true)]. You should then
receive an ExcelDna.Integration.ExcelReference object when your
function is called with a reference or a name referring to a reference
(you'll have to deal with cases where the function is called with a
value or name that refers to a value). With the ExcelReference object
you can get the first name for the referenced range using a call to
Excel(xlfGetDef, ... myRangeAddress ...). The address must be R1C1
style text address for the range - some xlf... function gives this
when passed in an ExcelReference. If you want to list all the names
defined in the book, you can call Excel(xlfNames, ...) to get back an
array of the names, and look for your range in there.

Anyway - it looks like you can get the same information in ExcelDna
that you get in VBA or Automation, though you have to be a bit careful
about what you are actually getting (and how you are using it).

If you need a more worked out example, let us know where you get
stuck.

Regards,
Govert

[Nemo]

Hi Govert,

Thanks for all the valuable info. I think you know more about
extending Excel than most of the MS engineers I have chatted
with. :-) The behavior you mentioned of calling with the address
as opposed to the range name is ok for our prototype application. Our
prototype creates named ranges on a sheet - we consider the range of
cells an object. Thus independent of how we are called addresses or
named range - we want the name of the range to help identify it.
However I was not aware the named range returns a name that has been
alphabetized. I had expected it to always return the range name it
was initially created with, since we create all the range name in our
app it would return our object names. I will obviously need to look
into the alphabetized name issue - since the user is free to create
additional range names based on our data and ranges. I will certainly
take a look at the ExcelReference items and the allow reference tag.

Thank You!!!!

> > TIA- Hide quoted text -
>
> - Show quoted text -