VBA 输出写入单元格 - #VALUE！错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16334195/
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
VBA Output Write To Cell - #VALUE! Error
提问by user2342380
I'm trying to make a UDF of mine work (in Excel 2003), and after debugging the problem appears to be summarized in this condensed version of my function:
我正在尝试使我的 UDF 工作（在 Excel 2003 中），并且在调试后问题似乎总结在我的函数的这个精简版本中：
Function btest(b_1 As Double) As Double
btest = 1
Worksheets("Sheet1").Range("A1").Value = b_1
'^this is the bit I want to work but doesn't^
End Function
This simulates my real function, which has a value assigned to it with no problems before the following cell output line which doesn't execute. I believe this is related to the #VALUE!error I get as a return, even though I used a MsgBoxwhich showed that the function did have a numeric value.
这模拟了我的真实函数，在不执行的以下单元格输出行之前为其分配了一个值，没有任何问题。我相信这与#VALUE!我作为回报得到的错误有关，即使我使用了MsgBox表明该函数确实具有数值的 a 。
Can anyone shed light on this please?
任何人都可以阐明这一点吗？
Also: what is the difference between
另外：有什么区别
Worksheets("Sheet1").Cells(1, 1) = B
and
和
Sheets("Sheet1").Range("A1").Value = B
where B is some numerical value?
其中 B 是某个数值？
Thanks
谢谢
采纳答案by brettdj
As you had already realised with
正如你已经意识到的
it looks like the problem is any UDF is not allowed to edit sheets, only return a single value...so if I want to edit another cell as part of the same process, I need to use a sub"
看起来问题是任何 UDF 都不允许编辑工作表，只返回一个值......所以如果我想在同一过程中编辑另一个单元格，我需要使用一个子“
a standardUDF can't change the sheet.
一个标准UDF不能改变片材。
But in terms of your follow-up comment
但就你的后续评论而言
Is this correct, and if so, how would I go about that - sub within a function or function within a sub? I want my spreadsheet to automatically react to inputs as it would with a function - no buttons or special actions required.
这是正确的，如果是这样，我将如何处理 - 函数中的 sub 或 sub 中的函数？我希望我的电子表格能够像使用函数一样自动对输入做出反应 - 不需要按钮或特殊操作。
You can use an Event
您可以使用事件
As an example:
举个例子：
- You want to track A1:A10 on a certain sheet for an input
- if this area is used you want to set
Worksheets("Sheet1").Range("A1").Valueto this value
- 您想在某个工作表上跟踪 A1:A10 以进行输入
- 如果使用此区域，您要设置
Worksheets("Sheet1").Range("A1").Value为该值
Question 1
问题 1
- right click the tab of the sheet you want to track
- View Code
- Copy and Paste in the code below 4 Press altf11to return to Excel
code
代码
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng1 As Range
Set rng1 = Intersect(Target, Range("a1:10"))
If rng1 Is Nothing Then Exit Sub
Application.EnableEvents = False
Worksheets("Sheet1").Range("A1").Value = rng1.Value
Application.EnableEvents = True
End Sub
Question 2
问题2
They are identical.
它们是相同的。
回答by chris neilsen
You are right (in your comment) that a UDF cannot changea sheet, only return a value.
您是对的（在您的评论中）UDF 不能更改工作表，只能返回一个值。
This holds true event if you call a Subor Functionfrom the UDF and try to have that change the sheet. It will fail too.
如果您从 UDF调用 aSub或Function并尝试更改工作表，则此事件为真。它也会失败。
Note: there is a (quite ugly) work around: see this answer
注意：有一个（相当丑陋的）解决方法：见这个答案
