Get Caller reference from excel

[ge deng]

I have a function have following code.

ExcelReference caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;

when I call this from excel it works fine, I get the caller reference back. However, when I call this way

object result = XlCall.Excel(XlCall.xlfEvaluate, "my function which calls caller");

the caller returns null. Does anybody know why this is happening? can I still get caller when the function is called from a macro?

Thanks

Jack

[Govert van Drimmelen]

Hi Jack,

What do you understand as the 'Caller' in that context?

xlfCaller can return a few different things, and will only return an ExcelReference when called from the worksheet. 

• If the custom function is entered in a single cell, CALLER returns the reference of that cell.
• If the custom function was part of an array formula entered in a range of cells, CALLER returns the reference of the range.
• If CALLER appears in a macro called by an Auto_Open, Auto_Close, Auto_Activate, or Auto_Deactivate macro, it returns the name of the calling sheet.
• If CALLER appears in a macro called by a command on a menu, it returns a horizontal array of three elements including the command's position number, the menu number, and the menu bar number.
• If CALLER appears in a macro called by an assigned-to-object macro, it returns the object identifier.
• If CALLER appears in a macro called by a tool on a toolbar, it returns a horizontal array containing the position number and the toolbar name.
• If CALLER appears in a macro called by an ON.DOUBLECLICK or ON.ENTRY function, CALLER returns the name of the chart object identifier or cell reference, if applicable, to which the ON.DOUBLECLICK or ON.ENTRY macro applies.
• If CALLER appears in a macro that was run manually, or for any reason not described above, it returns the #REF! error value.

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of ge deng [gde...@gmail.com]
Sent: 23 October 2015 12:11 PM
To: Excel-DNA
Subject: [ExcelDna] Get Caller reference from excel

--
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 http://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[ge deng]

Govert,

Thank you very much for your explanation. Sorry for my late reply because I have difficulty access google group from China. As a matter of fact I'm pretty new to the DNA world. There are tons of things I need to learn. Let me tell you what exactly I would like to do and maybe you can have a better solution.

I would like to measure the time of running any excel functions including UDF. The way how I do it right now is to get formula from the cell use following code:

string formulaText = (string)XlCall.Excel(XlCall.xlfGetCell, 41, formulaeRef);

and use following code to trigger the function:

object result = XlCall.Excel(XlCall.xlfEvaluate, formulaText);

That's where I got the problem because some of the formula use the get caller code but returns null when I call them this way. 

Do you know there is anyway to get around it? I'm just thinking loud, is there a way to set the caller to excel before I call the functions? Or you have a better solution to time to functions?

Thank you very much for your help.

Jack

To post to this group, send email to exc...@googlegroups.com.