VBA - 通过用户定义的函数更新其他单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12501759/
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 - Update Other Cells via User-Defined Function
提问by user1684104
I have a UDF(User-Defined Function) in VBA that needs to modify cell range on Excel.
我在 VBA 中有一个 UDF（用户定义函数）需要修改 Excel 上的单元格范围。
Since a UDF cannot do this, I tried using Event calls.
由于 UDF 无法执行此操作，因此我尝试使用事件调用。
When I raise a Custom Event and try to write to cells, I get #Value error. On the other hand, Application events such as
Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)can write to cells.
当我引发自定义事件并尝试写入单元格时，出现 #Value 错误。另一方面，诸如Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)可以写入单元格的应用程序事件
。
My questions is how do I update other cells by calling a UDF?
我的问题是如何通过调用 UDF 更新其他单元格？
回答by Daniel
Here is a way you can circumvent the restraint, you must do it indirectly. Method copied from Excel - How to fill cells from User Defined Function?:
这是您可以绕过限制的方法，您必须间接进行。从Excel复制的方法- 如何从用户定义函数填充单元格？：
In a standard module:
在标准模块中：
Public triggger As Boolean
Public carryover As Variant
Function reallysimple(r As Range) As Variant
triggger = True
reallysimple = r.Value
carryover = r.Value / 99
End Function
In worksheet code:
在工作表代码中：
Private Sub Worksheet_Calculate()
If Not triggger Then Exit Sub
triggger = False
Range("C1").Value = carryover
End Sub
This could be expanded for your purposes. Essentially, the UDF updates public variables which are then read from the Worksheet_Calculateevent to do... anything you like.
这可以根据您的目的进行扩展。本质上，UDF 更新公共变量，然后从Worksheet_Calculate事件中读取这些变量以执行...任何您喜欢的操作。
Another more complicated approach would be to write a vbscript file from your function that will attempt to automate Excel and run it via Shell. However, the method I listed above is much more reliable.
另一种更复杂的方法是从您的函数中编写一个 vbscript 文件，该文件将尝试自动化 Excel 并通过 Shell 运行它。但是，我上面列出的方法要可靠得多。
回答by Cem Firat
If call other function with Application.Evaluatemethod in your UDF function
you can change everything on sheet (Values,Steel,Etc.) because VBA does not know which function is called.
如果Application.Evaluate在 UDF函数中使用方法调用其他函数，则可以更改工作表上的所有内容（值、钢等），因为 VBA 不知道调用了哪个函数。
Example:
例子：
Sub UDFfunction()
Evaluate "otherfunc(""abc"")"
End Sub
Public Function otherfunc(ByVal str As String)
ActiveSheet.Cells(1, 1).Value = str
End Function
