VBA：如何通过函数更改另一个单元格的值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3844792/
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: How to change the value of another cell via a function?
提问by Vantomex
I'm an Excel VBA newbie.
我是 Excel VBA 新手。
How to change the value of the specified cell via a user-defined function? What's wrong with this code:
如何通过用户定义的函数更改指定单元格的值？这段代码有什么问题：
Function Test(ByVal ACell As Range) As String
ACell.Value = "This text is set by a function"
Test := "Result"
End Function
My wish is ... when I type =Test(E6)in cell E1, Excel will display the specified text in E6.
我的愿望是...当我=Test(E6)在单元格 E1 中键入时，Excel 将在 E6 中显示指定的文本。
采纳答案by Charles Williams
Excel VBA will not allow a user-defined function to alter the value of another cell. The only thing a UDF is allowed to do (with a few minor exceptions) is to return values to the cells it is called from.
Excel VBA 不允许用户定义的函数更改另一个单元格的值。UDF 唯一允许做的事情（有一些小例外）是将值返回到调用它的单元格。
回答by Charles Williams
A VBA UDF can be used as an array function to return results to multiple adjacent cells. Enter the formula into E1 and E2 and press Ctrl-Shift-Enter to create a multi-cell array formula. Your UDF would look something like this:
VBA UDF 可用作数组函数以将结果返回到多个相邻单元格。在 E1 和 E2 中输入公式，然后按 Ctrl-Shift-Enter 以创建多单元格数组公式。你的 UDF 看起来像这样：
Public Function TestArray(rng As Range)
Dim Ansa(1 To 2, 1 To 1) As Variant
Ansa(1, 1) = "First answer"
Ansa(2, 1) = "Second answer"
TestArray = Ansa
End Function
回答by Przemyslaw Remin
YES, of course, it is possible.
是的，当然，这是可能的。
Put this code in Module1 of VBA editor:
将此代码放在 VBA 编辑器的 Module1 中：
Function UDF_RectangleArea(A As Integer, B As Integer)
Evaluate "FireYourMacro(" & Application.Caller.Offset(0, 1).Address(False, False) & "," & A & "," & B & ")"
Rectangle = "Hello world"
End Function
Private Sub FireYourMacro(ResultCell As Range, A As Integer, B As Integer)
ResultCell = A * B
End Sub
The result of this example UDF is returned in another, adjacent cell. The user defined function UDF_RectangleAreacalculates the rectangle area based on its two parameters Aand Band returns result in a cell to the right. You can easily modify this example function.
此示例 UDF 的结果在另一个相邻单元格中返回。用户定义的函数UDF_RectangleArea来计算基于它的两个参数的矩形区域A和B并返回导致向右的细胞。您可以轻松修改此示例函数。
The limitation Microsoft imposed on function is bypassed by the use of VBA Evaluatefunction. Evaluate simply fires VBA macro from within UDF. The reference to the cell is passed by Application.Caller. Have fun!
通过使用 VBA评估函数绕过了 Microsoft 对函数施加的限制。Evaluate 只是从 UDF 中触发 VBA 宏。对单元格的引用由 传递Application.Caller。玩得开心！
UDF limitation documentation: https://support.microsoft.com/en-us/help/170787/description-of-limitations-of-custom-functions-in-excel
UDF 限制文档：https: //support.microsoft.com/en-us/help/170787/description-of-limitations-of-custom-functions-in-excel
回答by Patrick Honorez
Why not just typing you formula in E6 then ? That's the Excel logic: put your formula where you want the result to appear.
为什么不直接在 E6 中输入公式呢？这就是 Excel 逻辑：将公式放在您希望结果出现的位置。
