Excel UDF可以触发宏或工作表事件吗? [英] Can an Excel UDF trigger a macro or worksheet event?
问题描述
我正在处理交互式Excel图表,您所要做的就是将鼠标悬停在选定的单元格上,以使用UDF更新图表.这样很好 但是,图表数据并不总是出现在连续的行上,因此我想使用Excel过滤器隐藏这些行,并将其从图表中删除.
I’m working on an interactive Excel chart where all you have to do is mouse over selected cells to update the chart using a UDF. This works fine, however the chart data does not always come on consecutive lines and I would like to use the Excel filter to hide those rows, removing them from the chart.
我有VBA代码可以隐藏行,并且已经尝试过:
I have the VBA code to hide the rows and I’ve tried:
- 将VBA代码插入UDF
- 从UDF调用单独的宏
- 创建工作表更改事件(基于UDF单元格 实际上发生了变化),但是当UDF更改 值.
- inserting the VBA code into the UDF
- calling a separate macro from the UDF
- creating a worksheet change event (based on the cell the UDF actually changes), but it doesn't trigger when the UDF changes the value.
UDF是否可以触发宏或工作表更改事件?
Is there a way that that a UDF can trigger either a macro or worksheet change event?
推荐答案
这是一个使用 Global 布尔型标记DoIt
的非常简单的示例:
Here is a very simple example using a Global Boolean flag called DoIt
:
- 当UDF
whatever()
想要调用子hello()
时,它将DoIt
设置为 True - 引发Calculate事件,并且看到
DoIt
为 True - Calculate事件宏将
DoIt
设置为 False 并调用hello()
- when the UDF
whatever()
want the subhello()
called, it setsDoIt
to True - the Calculate event is raised and sees the
DoIt
is True - the Calculate event macro sets
DoIt
to False and callshello()
在工作表代码区域:
Private Sub Worksheet_Calculate()
If DoIt Then
DoIt = False
Call hello
End If
End Sub
在标准模块中:
Public DoIt As Boolean
Public Function whatever(r As Range) As String
whatever = ""
If r.Value <> "" Then
whatever = r.Text
DoIt = True
End If
End Function
Public Sub hello()
MsgBox "HELLO"
End Sub
这篇关于Excel UDF可以触发宏或工作表事件吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!