VBA输出写入单元格 - #VALUE！错误 [英] VBA Output Write To Cell - #VALUE! Error

问题描述

我正在尝试制作一个UDF的工作（在Excel 2003中），调试后，问题似乎总结在我的函数的这个精简版本中：

 函数btest（b_1 As Double）As Double 
 btest = 1 
工作表（Sheet1）。Range（A1）。Value = b_1 
'^这是我想要工作的位，但不是^ 
结束功能
  

这模拟了我的实际功能，它在没有执行的以下单元格输出行之前没有分配给它的值。我相信这与 #VALUE！错误相关，即使我使用了一个 MsgBox 表示该功能确实有一个数字值。

任何人都可以看到这一点吗？

另外：

 工作表（Sheet1）有什么区别？单元格（1，1）= B 
  

和

  Sheet1）。范围（A1）。值= B 
  

其中
B是一些数值？

谢谢

解决方案

实现与

它看起来像是问题是UDF不允许编辑表，只返回一个值...所以如果我要编辑另一个单元格作为同一进程的一部分，我需要使用子

a 标准 UDF无法更改工作表。

但是在您的后续评论方面

这是否正确，如果是这样，我将如何去做 - sub在子内的功能或功能内？我想要我的电子表格自动对输入做出反应，因为它将具有一个功能 - 不需要按钮或特殊操作。

您可以使用事件

例如：

• 您要跟踪A1：A10 on一个输入表单


• 如果这个区域被使用，你想设置工作表（Sheet1）。范围（A1）。值到这个值

问题1

1. 右键单击要跟踪的工作表的选项卡


2. 查看代码


3. 复制并粘贴以下代码
   4按 alt < a href =/ questions / tagged / f11class =post-tagtitle =显示有问题的标签'f11' =tag> f11 返回到Excel

代码

  Private Sub Worksheet_Change（ByVal Target As Ra nge）
 Dim rng1 As Range 
 Set rng1 = Intersect（Target，Range（a1：10））
如果rng1是Nothing然后退出Sub 
 Application.EnableEvents = False 
工作表（Sheet1）。Range（A1）。Value = rng1.Value 
 Application.EnableEvents = True 
 End Sub 
  / pre> 
 
 
 问题2  
 
 
 
它们是一样的。
 
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:
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 MsgBox which showed that the function did have a numeric value.

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?

Thanks
解决方案
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"
a standard UDF can't change the sheet.

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.
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").Value to this value


Question 1

right click the tab of the sheet you want to track
View Code
Copy and Paste in the code below
4  Press altf11 to 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

They are identical.

                        
这篇关于VBA输出写入单元格 -  #VALUE！错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！