我想处理Excel的Application.AfterCalculate事件。
这似乎只能使用类来完成。打开工作簿时,如何定义和实例化这样的事件处理程序?
将以下代码放入ThisWorkbook
对象中。
Public WithEvents appEventHandler As Application
Private Sub Workbook_Open()
Set appEventHandler = Application
End Sub
Private Sub appEventHandler_AfterCalculate()
' DO WHATEVER YOU WANT After Calculate. E.g., call a Public Subroutine.
MsgBox "AfterCalculate called"
End Sub
如果AfterCalculate处理程序修改了电子表格中的任何内容,那么为了防止意外的重复或递归回调,我将其封装在If
语句中,以确保它只运行一次,如以下模块代码所示:
Public RunningAfterCalculate As Boolean
Public Sub RunAfterCalculate()
If Not RunningAfterCalculate Then
RunningAfterCalculate = True
' DO WHATEVER YOU WANT AfterCalculate
' ...
RunningAfterCalculate = False
End If
End Sub
请注意,如果VBA由于某种原因遇到未处理的异常,则必须重置appEventHandler
——例如,通过运行以下子命令:
Public Sub RestartEventHandler()
Set ThisWorkbook.appEventHandler = Application
Application.EnableEvents = True ' Just in case...
RunningAfterCalculate = False
End Sub