Refer to selected cell in custom function

[Guest]

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?

 

[Juan Pablo González]

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

 

[Guest]

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.

 

[Tim Williams]

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.

 

[Tim Williams]

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.

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

--
Regards

Juan Pablo González

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])"

 

[Guest]

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

Regards

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.

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?