使用 VBA 创建自定义事件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25465642/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Creating A Custom Event With VBA
提问by Gabriel
I'm trying struggling to understand how to create a custom event using class modules in VBA.
我正在努力理解如何使用 VBA 中的类模块创建自定义事件。
I've put together the simple following example. You put a value in A1 and B1 and then re activate the sheet to calculate the sum of the two and then I hoped an event would fire to warn of calculation, but nothing happens.
我整理了下面这个简单的例子。您在 A1 和 B1 中输入一个值，然后重新激活工作表以计算两者的总和，然后我希望触发一个事件以警告计算，但没有任何反应。
I'd be very grateful for any help solving this example.
对于解决此示例的任何帮助，我将不胜感激。
Class module cCalc:
类模块 cCalc：
Dim m_wks As Worksheet
Public Event BeforeCalc()
Property Set Worksheet(wks As Worksheet)
Set m_wks = wks
End Property
Public Sub Calc()
Dim dVal1 As Double
Dim dVal2 As Double
With m_wks
dVal1 = .Range("A1").Value
dVal2 = .Range("B1").Value
RaiseEvent BeforeCalc
.Range("C1").Value = dVal1 + dVal2
End With
End Sub
In a module mGlobal:
在模块 mGlobal 中：
Public gCalc As cCalc
In the code behind Sheet1:
在 Sheet1 后面的代码中：
Private WithEvents calcEvent As cCalc
Private Sub calcEvent_BeforeCalc()
MsgBox "About to Calc!", vbInformation
End Sub
Private Sub Worksheet_Activate()
Set gCalc = New cCalc
Set gCalc.Worksheet = ActiveSheet
gCalc.Calc
End Sub
回答by Brian B.
You can't declare event-driven classes in modules. You'll need to set the cCalc reference in gModule equal to the object you declared WithEvents in Sheet1. Change your code in Sheet1 to what i wrote below and it will work:
您不能在模块中声明事件驱动类。您需要将 gModule 中的 cCalc 引用设置为等于您在 Sheet1 中声明的 WithEvents 对象。将 Sheet1 中的代码更改为我在下面写的内容，它将起作用：
Private WithEvents calcEvent As cCalc
Private Sub calcEvent_BeforeCalc()
MsgBox "About to Calc!", vbInformation
End Sub
Private Sub Worksheet_Activate()
Set calcEvent = New cCalc 'Instantiate the WithEvents object above
Set mGlobal.gCalc = calcEvent 'Set the object declared in gModule
Set mGlobal.gCalc.Worksheet = ActiveSheet
mGlobal.gCalc.Calc
End Sub
Note that this is using the variable you put in gModule... The event that is actually called is still calcEvent_BeforeCalc(), which is good as this way you can have n number of objects defined in gModule that would all fire off the same event code when the event is triggered.
请注意，这是使用您放入 gModule 的变量...实际调用的事件仍然是calcEvent_BeforeCalc()，这很好，因为这样您可以在 gModule 中定义 n 个对象，这些对象都将触发相同的事件事件触发时的代码。
To simplify the code, you could always just write:
为了简化代码，你总是可以这样写：
Private Sub Worksheet_Activate()
Set calcEvent = New cCalc
Set calcEvent.Worksheet = ActiveSheet
calcEvent.Calc
End Sub
