Use Application.Caller to return the range where the function is being
called from.

--
Regards

Juan Pablo González

"Snowsride" wrote in message
...
When I use an Excel function I type =FunctionName( I then move the
active
cell to the one on which I want to perform the calculation and then type )
and hit enter.

I want to create a custom function but can't figure out how to include a
reference to the cell on which the formula is to be performed. I've
recorded
a macro using a built in Excel function thus:

Cell D2 is the active cell and I enter the formula =Weeknum(a2) which
Excel
records as

ActiveCell.FormulaR1C1 = "=WEEKNUM(RC[-3])"

But this will only work if the cell to be referenced is 3 colums to the
left
of the cell that holds the formula. I want to use the function in any
cell
and perform it's calculation on any other cell i.e. I wnat to use my
function
like any other Excel function.

How can I do this please?