UDF Knows Range

  • Thread starter Thread starter Zone
  • Start date Start date
Z

Zone

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
 
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
 
Zone,

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

HTH,
Bernie
MS Excel MVP
 
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
 
A udf cannot directly change the value any cell other than the one in which
you call it.
 
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
 
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
 
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.
 
Interesting, Dave, but a bit longer than emulating the OP's original desire
to use something like
W(2) <bg>
 
Back
Top