You can pass the range to be used as a parameter to your function; here is a
very simple example:

Public Function MyFunction(MyRange As Range) As String

MyFunction = "Cell Contains " & MyRange.Value

End Function

This is the general method for supplying a user-specified cell (or range of
cells) to a function; your function can then refer to the range in several
ways: as an object, with all the methods and properties; by address
(MyRange.Address) or by contents (MyRange.Value) and use these in any
calculations.

"Snowsride" wrote:

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?