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

问题描述

我在VBA中有一个UDF（用户定义的函数），需要修改Excel上的单元格范围。

由于UDF无法执行此操作，我尝试使用事件调用。

当我提出自定义事件并尝试写入单元格时，我会收到#Value错误。另一方面，应用程序事件如
Private Sub App_SheetChange（ByVal Sh As Object，ByVal Target As Range）可以写入单元格。我的问题是如何通过调用UDF来更新其他单元格？

解决方案

这是一种可以规避克制的方式，你必须间接地做到这一点。方法从本网站：

在标准模块中：

 公开触发器作为布尔值
公开结转作为变量
函数reallysimple（r As Range）As Variant 
 triggersger = True 
 reallysimple = r.Value 
 carryover = r。价值/ 99 
结束功能
  

在工作表代码中：

  Private Sub Worksheet_Calculate（）
如果没有触发器然后退出Sub 
触发器= False 
范围（C1）。 = carryover 
 End Sub 
  

这可以扩展为您的目的。基本上，UDF更新公共变量，然后从 Worksheet_Calculate 事件中读取任何您喜欢的任何内容。

<另一个更复杂的方法是从函数中编写一个vbscript文件，该文件将尝试自动化Excel并通过Shell运行它。然而，上面列出的方法更可靠。

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

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

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.

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

解决方案

Here is a way you can circumvent the restraint, you must do it indirectly. Method copied from this website:

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_Calculate event to do... anything you like.

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屋！