Assigning cell values within a VBA program

[Guest]

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????

 

[Frank Kabel]

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

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.

 

[Trevor Shuttleworth]

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

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.