VBA - 通过用户定义的函数更新其他单元格 [英] VBA - Update Other Cells via User-Defined Function

问题描述

我在 VBA 中有一个 UDF(用户定义函数)需要修改 Excel 上的单元格范围.

I have a UDF(User-Defined Function) in VBA that needs to modify cell range on Excel.

由于 UDF 无法做到这一点，我尝试使用事件调用.

Since a UDF cannot do this, I tried using Event calls.

当我引发自定义事件并尝试写入单元格时，出现 #Value 错误.另一方面，应用程序事件，例如Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range) 可以写入单元格.

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.

我的问题是如何通过调用 UDF 更新其他单元格?

My questions is how do I update other cells by calling a UDF?

推荐答案

这里有一个方法可以绕过约束，必须间接进行.方法复制自 Excel - 如何从用户定义函数填充单元格?:

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?:

在标准模块中:

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

在工作表代码中:

Private Sub Worksheet_Calculate()
    If Not triggger Then Exit Sub
    triggger = False
    Range("C1").Value = carryover
End Sub

这可以根据您的目的进行扩展.本质上，UDF 更新公共变量，然后从 Worksheet_Calculate 事件中读取这些变量以执行...任何您喜欢的操作.

This could be expanded for your purposes. Essentially, the UDF updates public variables which are then read from the Worksheet_Calculate event to do... anything you like.

另一种更复杂的方法是从您的函数中编写一个 vbscript 文件，该文件将尝试自动化 Excel 并通过 Shell 运行它.但是，我上面列出的方法要可靠得多.

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.

这篇关于VBA - 通过用户定义的函数更新其他单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！