UDF Knows Range
 

I want my UDF to add a number to the value of the cell to the left of the
cell invoking the UDF. I don't want to specify the location of the cell. I
want the UDF to know what it is. So, if I put
=MyUdf(2) in cell B2, B2 would contain the value of A2 + 2. I thought this
would be Application.Caller, but I can't figure it out! TIA, James

UDF Knows Range
 

Hi James,
Make sure your UDF is a public function in the module that you have. Below
is your function code..

Public Function UDF(myNum As Integer) As Integer
UDF = ActiveCell.Offset(0, -1) + myNum
End Function
--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!


"Zone" wrote:

I want my UDF to add a number to the value of the cell to the left of the
cell invoking the UDF. I don't want to specify the location of the cell. I
want the UDF to know what it is. So, if I put
=MyUdf(2) in cell B2, B2 would contain the value of A2 + 2. I thought this
would be Application.Caller, but I can't figure it out! TIA, James

UDF Knows Range
 

Zone,

Function MyUdf(myAdd As Integer) As Double
MyUdf = Application.Caller.Offset(0, -1).Value + myAdd
End Function

HTH,
Bernie
MS Excel MVP


"Zone" wrote in message ...
I want my UDF to add a number to the value of the cell to the left of the cell invoking the UDF. I
don't want to specify the location of the cell. I want the UDF to know what it is. So, if I put
=MyUdf(2) in cell B2, B2 would contain the value of A2 + 2. I thought this would be
Application.Caller, but I can't figure it out! TIA, James

UDF Knows Range
 

The Activecell will change as the selection changes, so when this recalcs, the answer will be
incorrect.

Function MyUdf(myAdd As Integer) As Double
MyUdf = Application.Caller.Offset(0, -1).Value + myAdd
End Function


HTH,
Bernie
MS Excel MVP


"Pranav Vaidya" wrote in message
...
Hi James,
Make sure your UDF is a public function in the module that you have. Below
is your function code..

Public Function UDF(myNum As Integer) As Integer
UDF = ActiveCell.Offset(0, -1) + myNum
End Function
--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!


"Zone" wrote:

I want my UDF to add a number to the value of the cell to the left of the
cell invoking the UDF. I don't want to specify the location of the cell. I
want the UDF to know what it is. So, if I put
=MyUdf(2) in cell B2, B2 would contain the value of A2 + 2. I thought this
would be Application.Caller, but I can't figure it out! TIA, James

UDF Knows Range
 

A udf cannot directly change the value any cell other than the one in which
you call it.


"Zone" wrote:

I want my UDF to add a number to the value of the cell to the left of the
cell invoking the UDF. I don't want to specify the location of the cell. I
want the UDF to know what it is. So, if I put
=MyUdf(2) in cell B2, B2 would contain the value of A2 + 2. I thought this
would be Application.Caller, but I can't figure it out! TIA, James

UDF Knows Range
 

You can't. A UDF can only return a value to the cell that called it. Have a
look here for a description of UDF's

http://www.cpearson.com/excel/Writin...ionsInVBA.aspx

Mike

"Zone" wrote:

I want my UDF to add a number to the value of the cell to the left of the
cell invoking the UDF. I don't want to specify the location of the cell. I
want the UDF to know what it is. So, if I put
=MyUdf(2) in cell B2, B2 would contain the value of A2 + 2. I thought this
would be Application.Caller, but I can't figure it out! TIA, James

UDF Knows Range
 

Thanks, Bernie! A question. I found that if A2 contained 3 and I put
=myudf(2) in B2, then B2 would contain 5, as I wanted. However, if I then
went back and changed A2 to 7, B2 would not update, but would continue to
show 5. I changed the UDF as follows:
Function MyUdf(myAdd As Integer) As Double
Application.Volatile
MyUdf = Application.Caller.Offset(0, -1).Value + myAdd
End Function
I actually don't understand application.volatile, but this seemed to work.
Comments? Thanks also to the others who replied. I should have explained
better. I mean that I want the cell containing the UDF to have a value of
one cell to the left plus the argument value. James

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Zone,

Function MyUdf(myAdd As Integer) As Double
MyUdf = Application.Caller.Offset(0, -1).Value + myAdd
End Function

HTH,
Bernie
MS Excel MVP


"Zone" wrote in message
...
I want my UDF to add a number to the value of the cell to the left of the
cell invoking the UDF. I don't want to specify the location of the cell.
I want the UDF to know what it is. So, if I put
=MyUdf(2) in cell B2, B2 would contain the value of A2 + 2. I thought
this would be Application.Caller, but I can't figure it out! TIA, James

UDF Knows Range
 

James,

Your explanation was clear.

Anyway, Excel doesn't know that the cell with the UDF call in it relies on another cell, so it's
calculation tree doesn't function correctly.

You can use the Application.Volatile, or use a wrapper like this

=IF(A2="","",MyUdf(2))

which will allow Excel to build the dependency tree correctly.

Still, a better way would be to use

=A2+2

which will work in exactly the same way as your UDF when copied to other cells.

HTH,
Bernie
MS Excel MVP


"Zone" wrote in message ...
Thanks, Bernie! A question. I found that if A2 contained 3 and I put
=myudf(2) in B2, then B2 would contain 5, as I wanted. However, if I then went back and changed
A2 to 7, B2 would not update, but would continue to show 5. I changed the UDF as follows:
Function MyUdf(myAdd As Integer) As Double
Application.Volatile
MyUdf = Application.Caller.Offset(0, -1).Value + myAdd
End Function
I actually don't understand application.volatile, but this seemed to work. Comments? Thanks also
to the others who replied. I should have explained better. I mean that I want the cell
containing the UDF to have a value of one cell to the left plus the argument value. James

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Zone,

Function MyUdf(myAdd As Integer) As Double
MyUdf = Application.Caller.Offset(0, -1).Value + myAdd
End Function

HTH,
Bernie
MS Excel MVP


"Zone" wrote in message ...
I want my UDF to add a number to the value of the cell to the left of the cell invoking the UDF.
I don't want to specify the location of the cell. I want the UDF to know what it is. So, if I
put
=MyUdf(2) in cell B2, B2 would contain the value of A2 + 2. I thought this would be
Application.Caller, but I can't figure it out! TIA, James

UDF Knows Range
 

disregard that comment - completely misread the question

"Duke Carey" wrote:

A udf cannot directly change the value any cell other than the one in which
you call it.


"Zone" wrote:

I want my UDF to add a number to the value of the cell to the left of the
cell invoking the UDF. I don't want to specify the location of the cell. I
want the UDF to know what it is. So, if I put
=MyUdf(2) in cell B2, B2 would contain the value of A2 + 2. I thought this
would be Application.Caller, but I can't figure it out! TIA, James

UDF Knows Range
 

Duke, my explanation used kinda tortured language. Pardon. Thank you for
your reply. James

"Duke Carey" wrote in message
...
disregard that comment - completely misread the question

"Duke Carey" wrote:

A udf cannot directly change the value any cell other than the one in
which
you call it.


"Zone" wrote:

I want my UDF to add a number to the value of the cell to the left of
the
cell invoking the UDF. I don't want to specify the location of the
cell. I
want the UDF to know what it is. So, if I put
=MyUdf(2) in cell B2, B2 would contain the value of A2 + 2. I thought
this
would be Application.Caller, but I can't figure it out! TIA, James

UDF Knows Range
 

Just to add to Bernie's advice to pass all the ranges that affect the UDF to the
UDF, you could use:

Function MyUdf(myCell as range, myAdd As Long) As Double
dim Temp as double

if isnumeric(mycell.cells(1).value) then
temp = mycell.cells(1).value
end if

MyUdf = temp + myAdd

End Function

Then you could use a function like:

=myUDF(a2,2)

====
I'm guessing that you're using this is a learning experience--not that you
really wanted to rewrite =SUM() or excel's addition operator.

Zone wrote:

Thanks, Bernie! A question. I found that if A2 contained 3 and I put
=myudf(2) in B2, then B2 would contain 5, as I wanted. However, if I then
went back and changed A2 to 7, B2 would not update, but would continue to
show 5. I changed the UDF as follows:
Function MyUdf(myAdd As Integer) As Double
Application.Volatile
MyUdf = Application.Caller.Offset(0, -1).Value + myAdd
End Function
I actually don't understand application.volatile, but this seemed to work.
Comments? Thanks also to the others who replied. I should have explained
better. I mean that I want the cell containing the UDF to have a value of
one cell to the left plus the argument value. James

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Zone,

Function MyUdf(myAdd As Integer) As Double
MyUdf = Application.Caller.Offset(0, -1).Value + myAdd
End Function

HTH,
Bernie
MS Excel MVP


"Zone" wrote in message
...
I want my UDF to add a number to the value of the cell to the left of the
cell invoking the UDF. I don't want to specify the location of the cell.
I want the UDF to know what it is. So, if I put
=MyUdf(2) in cell B2, B2 would contain the value of A2 + 2. I thought
this would be Application.Caller, but I can't figure it out! TIA, James




--

Dave Peterson

UDF Knows Range
 

Interesting, Dave, but a bit longer than emulating the OP's original desire
to use something like
W(2) <bg
"Dave Peterson" wrote in message
...
Just to add to Bernie's advice to pass all the ranges that affect the UDF
to the
UDF, you could use:

Function MyUdf(myCell as range, myAdd As Long) As Double
dim Temp as double

if isnumeric(mycell.cells(1).value) then
temp = mycell.cells(1).value
end if

MyUdf = temp + myAdd

End Function

Then you could use a function like:

=myUDF(a2,2)

====
I'm guessing that you're using this is a learning experience--not that you
really wanted to rewrite =SUM() or excel's addition operator.

Zone wrote:

Thanks, Bernie! A question. I found that if A2 contained 3 and I put
=myudf(2) in B2, then B2 would contain 5, as I wanted. However, if I
then
went back and changed A2 to 7, B2 would not update, but would continue to
show 5. I changed the UDF as follows:
Function MyUdf(myAdd As Integer) As Double
Application.Volatile
MyUdf = Application.Caller.Offset(0, -1).Value + myAdd
End Function
I actually don't understand application.volatile, but this seemed to
work.
Comments? Thanks also to the others who replied. I should have
explained
better. I mean that I want the cell containing the UDF to have a value
of
one cell to the left plus the argument value. James

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Zone,

Function MyUdf(myAdd As Integer) As Double
MyUdf = Application.Caller.Offset(0, -1).Value + myAdd
End Function

HTH,
Bernie
MS Excel MVP


"Zone" wrote in message
...
I want my UDF to add a number to the value of the cell to the left of
the
cell invoking the UDF. I don't want to specify the location of the
cell.
I want the UDF to know what it is. So, if I put
=MyUdf(2) in cell B2, B2 would contain the value of A2 + 2. I thought
this would be Application.Caller, but I can't figure it out! TIA,
James




--

Dave Peterson

UDF Knows Range
 

I'm guessing that you're using this is a learning experience--not that you
really wanted to rewrite =SUM() or excel's addition operator.

Zone wrote:

Interesting, Dave, but a bit longer than emulating the OP's original desire
to use something like
W(2) <bg
"Dave Peterson" wrote in message
...
Just to add to Bernie's advice to pass all the ranges that affect the UDF
to the
UDF, you could use:

Function MyUdf(myCell as range, myAdd As Long) As Double
dim Temp as double

if isnumeric(mycell.cells(1).value) then
temp = mycell.cells(1).value
end if

MyUdf = temp + myAdd

End Function

Then you could use a function like:

=myUDF(a2,2)

====
I'm guessing that you're using this is a learning experience--not that you
really wanted to rewrite =SUM() or excel's addition operator.

Zone wrote:

Thanks, Bernie! A question. I found that if A2 contained 3 and I put
=myudf(2) in B2, then B2 would contain 5, as I wanted. However, if I
then
went back and changed A2 to 7, B2 would not update, but would continue to
show 5. I changed the UDF as follows:
Function MyUdf(myAdd As Integer) As Double
Application.Volatile
MyUdf = Application.Caller.Offset(0, -1).Value + myAdd
End Function
I actually don't understand application.volatile, but this seemed to
work.
Comments? Thanks also to the others who replied. I should have
explained
better. I mean that I want the cell containing the UDF to have a value
of
one cell to the left plus the argument value. James

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Zone,

Function MyUdf(myAdd As Integer) As Double
MyUdf = Application.Caller.Offset(0, -1).Value + myAdd
End Function

HTH,
Bernie
MS Excel MVP


"Zone" wrote in message
...
I want my UDF to add a number to the value of the cell to the left of
the
cell invoking the UDF. I don't want to specify the location of the
cell.
I want the UDF to know what it is. So, if I put
=MyUdf(2) in cell B2, B2 would contain the value of A2 + 2. I thought
this would be Application.Caller, but I can't figure it out! TIA,
James




--

Dave Peterson

--

Dave Peterson

UDF Knows Range
 

Yes, Dave, you're right, of course. And I did learn something.

"Dave Peterson" wrote in message
...
I'm guessing that you're using this is a learning experience--not that
you
really wanted to rewrite =SUM() or excel's addition operator.

Zone wrote:

Interesting, Dave, but a bit longer than emulating the OP's original
desire
to use something like
W(2) <bg
"Dave Peterson" wrote in message
...
Just to add to Bernie's advice to pass all the ranges that affect the
UDF
to the
UDF, you could use:

Function MyUdf(myCell as range, myAdd As Long) As Double
dim Temp as double

if isnumeric(mycell.cells(1).value) then
temp = mycell.cells(1).value
end if

MyUdf = temp + myAdd

End Function

Then you could use a function like:

=myUDF(a2,2)

====
I'm guessing that you're using this is a learning experience--not that
you
really wanted to rewrite =SUM() or excel's addition operator.

Zone wrote:

Thanks, Bernie! A question. I found that if A2 contained 3 and I put
=myudf(2) in B2, then B2 would contain 5, as I wanted. However, if I
then
went back and changed A2 to 7, B2 would not update, but would continue
to
show 5. I changed the UDF as follows:
Function MyUdf(myAdd As Integer) As Double
Application.Volatile
MyUdf = Application.Caller.Offset(0, -1).Value + myAdd
End Function
I actually don't understand application.volatile, but this seemed to
work.
Comments? Thanks also to the others who replied. I should have
explained
better. I mean that I want the cell containing the UDF to have a
value
of
one cell to the left plus the argument value. James

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Zone,

Function MyUdf(myAdd As Integer) As Double
MyUdf = Application.Caller.Offset(0, -1).Value + myAdd
End Function

HTH,
Bernie
MS Excel MVP


"Zone" wrote in message
...
I want my UDF to add a number to the value of the cell to the left
of
the
cell invoking the UDF. I don't want to specify the location of the
cell.
I want the UDF to know what it is. So, if I put
=MyUdf(2) in cell B2, B2 would contain the value of A2 + 2. I
thought
this would be Application.Caller, but I can't figure it out! TIA,
James




--

Dave Peterson

--

Dave Peterson