Why doesn't this function work

Function Zemax(
Worksheets("Sheet1").Range("A1").Formula = "=2+2
Zemax =
End Functio

If I execute it within the IDE using F5, I get cell A1 set correctly
If I comment out the second line, and call it as a function reference within the spreadsheet it sets the current cell to 3
If I don't comment it out, it doesn't set cell A1, and returns a #VALUE error

What gives????

Hi
a functions used within a worksheet can't change other cells or
formats. So if you invoke this from your worksheet it can't work. No
chance to achieve this.

--
Regards
Frank Kabel
Frankfurt, Germany

"Chris Burrows" schrieb im Newsbeitrag
...
Why doesn't this function work?

Function Zemax()
Worksheets("Sheet1").Range("A1").Formula = "=2+2"
Zemax = 3
End Function

If I execute it within the IDE using F5, I get cell A1 set correctly.
If I comment out the second line, and call it as a function reference
within the spreadsheet it sets the current cell to 3.
If I don't comment it out, it doesn't set cell A1, and returns a
#VALUE error.

What gives?????

Chris

a Function can only return a value to the cell it is in; it cannot affect
another cell or the Excel environment.

Regards

Trevor


"Chris Burrows" wrote in message
...
Why doesn't this function work?

Function Zemax()
Worksheets("Sheet1").Range("A1").Formula = "=2+2"
Zemax = 3
End Function

If I execute it within the IDE using F5, I get cell A1 set correctly.
If I comment out the second line, and call it as a function reference
within the spreadsheet it sets the current cell to 3.
If I don't comment it out, it doesn't set cell A1, and returns a #VALUE
error.

What gives?????