vba 如何从VBA写入单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4168679/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to write to a cell from VBA
提问by tbischel
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:
即使对于 VBA 新手来说，这似乎也是一个愚蠢的问题，但我不知道如何在 VBA 中写入单元格......我在“Module1”中有一个如下所示的函数：
Function Foo(bar As Boolean)
Range("A1").Value = 1
Foo = ...
End Function
Then say I set Cell A2's formula to:
然后说我将 Cell A2 的公式设置为：
=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???
如果我取出范围设置行，调用本身就可以工作……在这种情况下，将调用单元格设置为 foo 值。但我希望最终一次写入大量单元格，而不是为每个单元格调用不同的函数。我究竟做错了什么？？？
If this code should work as is... are there settings in Excel 2007 that might be blocking editing a cell, or something like that?
如果此代码应按原样工作... Excel 2007 中是否有可能阻止编辑单元格或类似内容的设置？
采纳答案by Richard Fawcett
回答by ja72
To create a custom function in VBA enter the following in a Module
要在 VBA 中创建自定义函数，请在模块中输入以下内容
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.
并在工作表上使用它，因为=MyCalc(A2)它返回并设置适当的值。根据您要执行的操作选择是否要传递值或范围。
