How to retrieve the location of the executed function?

Feb 28, 2011 at 1:47 PM

Hi,

I would like to find a way to retrieve the cell location of a function when it is executed.

To illustrate what I am trying to do, please look at this example.

We assume that we have two functions:

 [ExcelFunction(Description = "Add", Category = "")]
 public static int Add(int number1, int number2)
 {

    return number1 + number2;

 } 

 [ExcelFunction(Description = "Sub", Category = "")]
 public static int Sub(int number1, int number2)
 {

    return number1 - number2;

 } 

In my excel spreadsheet, I have the following:

Cell A1: =Add(B1,C1)

Cell A2: =Sub(B1,C1)

When I change the value of the cell B1 or C1, both "Add" and "Sub" function are executed.

When the "Add" is executed, I would like to find a way to know that the location of the executed function is "A1".

When the "Sub" is executed, I would like to find a way to know that the location of the executed function is "A2".

 

My final objective is the following : my real function will return an array of values and I want to write these values into multiple cells, one row after the row where the function is.

I found a way to write the values outside the cell of the executed function by using the "AfterCalculate" event. But I didn't find a way to retrieve the position of the executed function to write the result automatically one row after.

Thank you for your help!

Fabien

 

 

 

Coordinator
Feb 28, 2011 at 2:10 PM

Hi Fabien,

You'd use XlCall.Excel(XlCall.xlfcaller) to get hold of the caller. If called from a cell it will return an object of type ExcelReference, which you can use in further XlCall API calls to get a formula for the caller etc. If you search on "xlfCaller" in the Google group (http://groups.google.com/group/exceldna) you'll find a few relevant posts.

I suggest you also look at the ArrayResizer example: http://excel-dna.net/2011/01/30/resizing-excel-udf-result-arrays/. It does something similar to what you want to do, and shows how to use the caller info to set up work to do later. Once you get that example working, you should be able to adapt to your requirements easily.

-Govert

Feb 28, 2011 at 3:10 PM

Thank you very much.

Your reply is very useful for me!

Fabien