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.