Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Set a cell from within a function
I am writing a function to calculate the area of a triangle given the lengths
of the sides. If the inputs are invalid (one side too big), I want the function to set CELL(3,3) to one. Setting CELL(3,3)=1 in the function doesnt work. How can I accomplish this? -- Gary's Student |
#2
|
|||
|
|||
Hi Gary's Student
what do you mean by "function" a "user defined function" written in VBA or a formula in a cell in a worksheet? if the later a formula can not affect another cell only the one it is in ... if it's a vba function then you can using code along the lines of Cell(3,3).value = 1 -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Gary's Student" wrote in message ... I am writing a function to calculate the area of a triangle given the lengths of the sides. If the inputs are invalid (one side too big), I want the function to set CELL(3,3) to one. Setting CELL(3,3)=1 in the function doesn't work. How can I accomplish this? -- Gary's Student |
#3
|
|||
|
|||
JulieD wrote:
... if it's a vba function then you can using code along the lines of Cell(3,3).value = 1 Am I correct though that you can only do that if you're using VBA to write a macro as opposed to a function? A VBA function can only return a single value and not alter spread sheet cells? While a macro can reach out and touch anything? I'm still trying to feel my way through some of this. Bill |
#4
|
|||
|
|||
Hi Bill
yes, you are correct - badly worded on my part .. .a VBA function can not make changes to another cell whereas a sub procedure (macro) can -- Cheers JulieD "Bill Martin -- (Remove NOSPAM from address)" wrote in message ... JulieD wrote: ... if it's a vba function then you can using code along the lines of Cell(3,3).value = 1 Am I correct though that you can only do that if you're using VBA to write a macro as opposed to a function? A VBA function can only return a single value and not alter spread sheet cells? While a macro can reach out and touch anything? I'm still trying to feel my way through some of this. Bill |
#5
|
|||
|
|||
Hi
better answer (thanks Bill) a function can not change the value of another cell - you need to use a VBA macro for this - for example you could code against the worksheet_change event that if the cell with the function in it returned a 0 or whatever, then Range(C3) needs to change to a 1. -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Gary's Student" wrote in message ... I am writing a function to calculate the area of a triangle given the lengths of the sides. If the inputs are invalid (one side too big), I want the function to set CELL(3,3) to one. Setting CELL(3,3)=1 in the function doesn't work. How can I accomplish this? -- Gary's Student |
#6
|
|||
|
|||
Thank you for your very rapid response
"JulieD" wrote: Hi better answer (thanks Bill) a function can not change the value of another cell - you need to use a VBA macro for this - for example you could code against the worksheet_change event that if the cell with the function in it returned a 0 or whatever, then Range(C3) needs to change to a 1. -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Gary's Student" wrote in message ... I am writing a function to calculate the area of a triangle given the lengths of the sides. If the inputs are invalid (one side too big), I want the function to set CELL(3,3) to one. Setting CELL(3,3)=1 in the function doesn't work. How can I accomplish this? -- Gary's Student |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If Function: Blank cell and 0 (zero) seem to have same "value" | Excel Worksheet Functions | |||
#VALUE in cell but pop up function box show right number | Excel Discussion (Misc queries) | |||
Function to operate on ink linked to cell | Excel Worksheet Functions | |||
Is there a function to determine whether a cell contains a formul. | Excel Worksheet Functions | |||
Excel - option to extend function in cell to column | Excel Worksheet Functions |