how to write to a cell from VBA

seems like a dumb question even for a VBA newbie, but I can't figure out how to write to a cell in VBA... I have a function in "Module1" that looks like this:

Function Foo(bar As Boolean)
  Range("A1").Value = 1
  Foo = ...
End Function

Then say I set Cell 开发者_如何学PythonA2's formula to:

=Foo(true)

The call itself works if I take out the range setting line... in that case setting the calling cell to the foo value. But I'd like this to eventually write a ton of cells at once rather than have a different function call for each cell. What am I doing wrong???

---

If this code should work as is... are there settings in Excel 2007 that might be blocking editing a cell, or something like that?

---

According to this page, it's not possible for an Excel user defined function to alter other parts of the Workbook. Looks like you may have to try another way.

---

To create a custom function in VBA enter the following in a Module

Public Function MyCalc(ByRef r as Range) as Double
    MyCalc = r.Value^2
End Function

or

Public Function MyCalc(ByVal x as Double) as Double
    MyCalc = x^2
End Function

and use it on a worksheet as =MyCalc(A2) which returns and sets the appropriate value. Choose if you want to pass a value, or a range depending on what you want to do.