Refer to selected cell in custom function
 

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?

Refer to selected cell in custom function
 

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?

Refer to selected cell in custom function
 

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?

Refer to selected cell in custom function
 

Place this in a standard module

function AddUp(rng as range, rng2 as range) as variant
on error goto haveError

AddUp=rng.value + rng2.value
exit function

haveError:
AddUp="Error!"


end function

In A1 on a worksheet in the same workbook enter

=AddUp(B1,C1)

Tim.




"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?

Refer to selected cell in custom function
 

Note that if you use application.caller in your function to address the
offset cell it may not recalculate if the value in the offset cell changes.
I think Excel only recalculates a formula when one of the inputs changes -
where "input" is actually a passed parameter and not one "internal" to the
function.

Tim.

"Juan Pablo González" wrote in message
...
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])"

Refer to selected cell in custom function
 

Thanks to K.Dale and Tim Williams - just the trick.

Regards

"Tim Williams" wrote:


Place this in a standard module

function AddUp(rng as range, rng2 as range) as variant
on error goto haveError

AddUp=rng.value + rng2.value
exit function

haveError:
AddUp="Error!"


end function

In A1 on a worksheet in the same workbook enter

=AddUp(B1,C1)

Tim.




"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?