从函数设置单元格值 [英] Set a cell value from a function

问题描述

然后，单元格 A1 的内容是 = test（2）其中 test 是函数：

Then contents of cell A1 is =test(2) where test is the function:

Function test(ByRef x As Double) As Double
Range("A2") = x
test = x * x
End Function

可以你解释为什么这会在单元格 A1 中给出 #VALUE！，而单元格 A2 ？我希望 A2 包含 2 和 A1 以包含 4 。没有行 Range（A2）= x 该函数按预期的方式工作（平均单元格的值）

Can you explain why this gives #VALUE! in cell A1 and nothing in cell A2? I expected A2 to contain 2 and A1 to contain 4. Without the line Range("A2") = x the function works as expected (squaring the value of a cell).

真正让我感到困惑的是如果用子程序 calltest 将 test 它有效：

What really confuses me is if you wrap test with the subroutine calltest then it works:

Sub calltest()
t = test(2)
Range("A1") = t
End Sub

Function test(ByRef x As Double) As Double
Range("A2") = x
test = x * x
End Function

但这不是

Function test(ByRef x As Double) As Double
Range("A2") = x
End Function

推荐答案

当您从工作表单元格中调用函数时，您正在有效地将该函数用作用户定义函数，其具有如下所述的限制：

When you call a function from a worksheet cell, you are effectively using the function as a User Defined Function, which has the limitations as described here:

http： //support.microsoft.com/kb/170787

在文字中有一行：

任何环境变化都应通过使用Visual
Basic子例程进行。

Any environmental changes should be made through the use of a Visual Basic subroutine.

有趣的是，他们如何使用应该而不是必须。我不知道KB的作者是否知道环境变化可以从VBA函数发生。

It's interesting how they use the word should rather than must. I wonder if the author of the KB knew that environment changes can happen from a VBA Function.

现在，当您从另一个VBA子/函数调用该函数时，它是治疗不同。从帮助文档（对不起，我找不到一个网页参考 - 基本上，在VBE，突出显示单词功能并按F1）：

Now, when you call the function from another VBA Sub / Function, it is treated differently. From the help docs (sorry I couldn't find a web page reference - basically, in VBE, highlight the word Function and press F1):

像子程序一样，函数过程是一个单独的程序
，它可以引用参数，执行一系列语句，并更改
参数的值。但是，与子程序不同，您可以使用
在
的右侧使用Function过程，与使用任何内在函数的方式相同，例如Sqr，Cos或Chr，
当你想使用函数返回的值。

Like a Sub procedure, a Function procedure is a separate procedure that can take arguments, perform a series of statements, and change the values of its arguments. However, unlike a Sub procedure, you can use a Function procedure on the right side of an expression in the same way you use any intrinsic function, such as Sqr, Cos, or Chr, when you want to use the value returned by the function.

所以听起来像Subs和函数可以做同样的事情，当使用在VBA中，除了函数可以将值返回给调用函数/ sub。

So it sounds like Subs and functions can do the same things when used in VBA only, except that Functions can return values back to the calling function/sub.

这是非常有趣的，实际上，因为Excel可以调用某种函数只读限制到Excel的环境。

This is pretty interesting, actually, since Excel can call a function with some sort of "Read-Only" restriction to Excel's environment.

我认为，最后，Excel可以以不同于VBA的方式从工作表单元格中调用该函数。当您从单元格中调用它时，它被认为是用户定义的函数，其中包括更改Excel环境的限制。在VBA（来自一个调用链的原始调用者来自VBA）中调用的位置，它具有Sub所有的功能，加上它可以返回值。

I think that, in the end, Excel can call the function from a worksheet cell in a different way than VBA does. When you call it from a Cell, it's considered a User Defined Function, which includes the restrictions of changing Excel's environment. Where when called from within VBA (where the original caller from a chain of calls is from VBA), it has all the power a Sub does, plus it can return values.

这篇关于从函数设置单元格值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！