Can an Excel UDF trigger a macro or worksheet even

问题:

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.

I have the VBA code to hide the rows and I’ve tried:

• 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.

Is there a way that that a UDF can trigger either a macro or worksheet change event?

回答1:

Here is a very simple example using a Global Boolean flag called DoIt:

• when the UDF whatever() want the sub hello() called, it sets DoIt to True
• the Calculate event is raised and sees the DoIt is True
• the Calculate event macro sets DoIt to False and calls hello()

In the worksheet code area:

Private Sub Worksheet_Calculate()
    If DoIt Then
        DoIt = False
        Call hello
    End If
End Sub

In a standard module:

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