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

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

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

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

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

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