Finding the cell reference using ExcelDna

[Jerome]

I recently asked Govert how to find an excel cell reference in
ExcelDna. He suggested something that I tested successfully.
So here is a small UDF in the ExcelDna framework that finds out in
which cell the UDF is running.
This is immensely useful for a variety of purposes, the main one for
me is to buid an object oriented financial model UDF library in C#
using ExcelDna. Finding out the cell reference enables me to keep a
clean dictionary of all objects created in the Excel memory.

Thanks Govert

Jerome

****************************************************************************************************************************
[ExcelFunction(IsMacroType=true, Description = "Returns calling cell
reference", Category = "GoLib")]
public static string CallingCellReference()
{

ExcelReference reference =
(ExcelReference)XlCall.Excel(XlCall.xlfCaller);
string cellReference =
(string)XlCall.Excel(XlCall.xlfAddress, 1+reference.RowFirst,
1+reference.ColumnFirst);

string sheetName = (string)XlCall.Excel(XlCall.xlSheetNm,
reference);
cellReference = sheetName + cellReference;

return cellReference;

}

[Francois Prunier]

Exactly what I was looking for. This should definitely go in the FAQ.

[J Anthelme]

Old post, but just in case :

For the same reasons as Jerome, I need to "keep a clean dictionary of all objects created in the Excel memory". But the above method does not work (I think) if the spreadsheet user changes the sheet addresses (for example by inserting rows and/or columns). What would be needed in this case is a "dynamic" way to keep track of the calling cells' reference, irrespective of their current "static" cell address.

How is this possible?

Thank you

[Govert van Drimmelen]

I suspect an object-handle style approach using the Excel RTD mechanism is what you need.

Then you could get an IDisposable.Dispose call to clean up.

One write-up of such an implementation is probably the one in the ACQ library.

See the write-up here, and look out for the object handler aspects:

https://www.codeproject.com/Articles/1097174/Interpolation-in-Excel-using-Excel-DNA

-Govert

[J Anthelme]

Thanks Govert. I'll look at the attached code (I am on F# but I am sure the same techniques would apply)

j

--
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.

--

Best

JA [ʒɑ̃]

[Govert van Drimmelen]

Here's an F# implementation of an object handler: https://github.com/mndrake/ExcelObjectHandler

-Govert