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