VBA输出写入单元格-#VALUE!错误

Question

我正在尝试让我的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

这是模拟我的真实功能，之前已经赋值并没有问题。但是，在下面不执行的单元格输出行之后，出现了“#VALUE!”错误。我认为这与我使用 MsgBox 显示函数具有数字值有关。

请问有谁能给解释一下吗？

另外: 这两个有什么区别呢?

Worksheets("Sheet1").Cells(1, 1) = B

并且

Sheets("Sheet1").Range("A1").Value = B

其中B是某个数值？

谢谢。

- user2342380

---

你想从VBA代码还是工作表单元格中调用你的函数？ - Kazimierz Jawor

另外，您可以也查看一下这个链接：https://dev59.com/GGUo5IYBdhLWcg3w7jEq#15659852。 - Kazimierz Jawor

看起来问题是任何用户定义函数都不允许编辑工作表，只能返回单个值...所以如果我想在同一过程中编辑另一个单元格，我需要使用子程序。 - user2342380

是的，如果您从Excel单元格调用此函数，则无法从函数中操作单元格... 如果您需要将值返回到多个单元格，请使用Sub。 如果您需要作为任何操作的结果执行此操作，则可以探索事件。 - Kazimierz Jawor

非常感谢大家。一个好的答案是链接，它启发我在特定于工作表的子程序中调用函数，以便每当目标被更改时运行。 - user2342380

Answer 1

正如您已经意识到的那样，问题似乎是任何UDF都不允许编辑工作表，只能返回单个值...所以如果我想在同一过程中编辑另一个单元格，我需要使用一个子程序。

一个标准的UDF不能改变工作表。

但是就您的后续评论而言：

这正确吗？如果是这样，我该怎么做——在函数内使用子函数还是在子函数内使用函数？我希望我的电子表格能够像使用函数一样自动响应输入——无需按钮或特殊操作。

您可以使用事件。

例如：

• 你想跟踪某个工作表上的A1:A10输入
• 如果使用了这个区域，你想将Worksheets("Sheet1").Range("A1").Value设置为这个值

问题1

1. 右键单击要跟踪的工作表标签
2. 选择“查看代码”
3. 复制并粘贴以下代码 4 按altf11返回Excel

代码

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

问题2

它们是相同的。

- brettdj

Answer 2

你在评论中说得对，UDF 不能 更改 工作表，只能返回一个值。

即使从 UDF 中调用 Sub 或 Function 并尝试更改工作表，这也是不行的。它也会失败。

注意：有一个（相当丑陋的）解决方法：请参见 this answer。

- chris neilsen