Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Addin:Setting the range to the Excel.Range object range prop | Excel Worksheet Functions | |||
x y scatter chart series ranges reset to x(range) = y(range) | Charts and Charting in Excel | |||
Selecting range in list of range names depending on a cell informa | Excel Discussion (Misc queries) | |||
formula to sort a range so that it matches the exact rows of a column that is outside that range? | Excel Discussion (Misc queries) | |||
How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions |