how to write to a cell from VBA

3
votes

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

2
excelvba
it's not exactly clear what you intend this function to do.. - Fosco
It seems odd that you are ignoring the errors parameter in your function. Is this your full code? - Richard Fawcett
its not important, all I'm trying to get to work is writing out cells from a VBA function that reacts to inputs from a range of other cells... I'll take out everything that doesn't matter for clarity - tbischel
+1, tho I wish the title was more descriptive - Jeremy Thompson

2 Answers

2
votes

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.

0
votes

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.