excel - 消费/订阅类模块的自定义事件

Question

我想使用我编写的类模块的事件。类模块如下所示

''CError64Row
Public Event ErrorClicked(ByVal row As Integer, ByVal column As Integer)

Public WithEvents lblDescription As MSForms.Label
Public WithEvents lblFile As MSForms.Label
Public WithEvents lblRow As MSForms.Label
Public WithEvents lblCol As MSForms.Label

Public row As Long
Public col As Long

Private Sub lblDescription_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
   RaiseEvent ErrorClicked(row, col)
End Sub

Private Sub lblFile_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
   RaiseEvent ErrorClicked(row, col)
End Sub

Private Sub lblRow_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
   RaiseEvent ErrorClicked(row, col)
End Sub

Private Sub lblCol_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
   RaiseEvent ErrorClicked(row, col)
End Sub

在我的一个表单中，我创建了 CError64Row 对象

Private m_Elements As Long

Private mErrors() As CError64Row

Private Sub UserForm_Initialize()
    m_Elements = 0
End Sub

Public Function SetError(text As String, filename As String, row As Integer, column As Integer) As String
    Dim ctl As control

   ReDim Preserve mErrors(m_Elements + 1)

    Dim errorRow As CError64Row
    Set errorRow = New CError64Row
    Set mErrors(m_Elements) = errorRow

    mErrors(m_Elements).row = row
    mErrors(m_Elements).col = column
    Set mErrors(m_Elements).lblDescription = Me.Controls.Add("forms.label.1")
    With mErrors(m_Elements).lblDescription
        .Left = 35
        .height = 14
        .Top = 18 + (m_Elements) * 14
        .width = 631
        .Caption = text
    End With

    Set mErrors(m_Elements).lblFile = Me.Controls.Add("forms.label.1")
    With mErrors(m_Elements).lblFile
        .Left = 665
        .height = 14
        .Top = 18 + (m_Elements) * 14
        .width = 106
        .Caption = filename
    End With

    Set mErrors(m_Elements).lblRow = Me.Controls.Add("forms.label.1")
    With mErrors(m_Elements).lblRow
        .Left = 770
        .height = 14
        .Top = 18 + (m_Elements) * 14
        .width = 36
        .Caption = CStr(row)
    End With

    Set mErrors(m_Elements).lblCol = Me.Controls.Add("forms.label.1")
    With mErrors(m_Elements).lblCol
        .Left = 805
        .height = 14
        .Top = 18 + (m_Elements) * 14
        .width = 36
        .Caption = CStr(column)
    End With
    m_Elements = m_Elements + 1
End Function

Public Sub CError64Row_ErrorClicked(ByVal row As Integer, ByVal column As Integer)
    MsgBox "MSG received"
End Sub

我想接收 ErrorClicked 事件，但我不知道如何订阅该事件。我在这个答案中读到Is it possible to create and handle a custom Event in a Customized UserForm?我可以通过

“订阅”

Private Sub [Provider]_MemberName

但我怀疑这是否适用于在方法中创建的对象。如何订阅在方法中创建的对象的事件？

Answer 1

Private mErrors() As CError64Row

您的“事件提供者”对象位于此 mErrors 数组中 - 您需要以某种方式声明它 WithEvents，但这将是非法的:

Private WithEvents mErrors() As CError64Row

问题不在于对象是在方法中创建的 - 如何创建对象没有区别。问题是您无法使表单处理由引用驻留在数组/集合中而不是 Private WithEvents 字段中的对象转发的事件。

解决方案是让自定义类与表单对话 - 并且您已经拥有对它的引用:

Private Sub lblDescription_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Dim parentForm As TheFormClass
    Set parentForm = lblDescription.Parent
    parentForm.HandleErrorClicked(row, col)
End Sub

现在，这将 TheFormClass 表单与自定义控件类紧密耦合，这并不理想 - 如果我们需要在其他表单中重用该类怎么办？

我们可以晚点出发:

Private Sub lblDescription_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Dim parentForm As Object
    Set parentForm = lblDescription.Parent
    parentForm.HandleErrorClicked(row, col)
End Sub

但是这样我们就失去了编译时验证，并且不能保证 parentForm 具有 HandleErrorClicked 方法 - 如果该方法不存在，我们就会失败出现错误 438。

除非......除非我们用一个接口(interface)来形式化它，比如一个非常简单的 IErrorView 类，它可能看起来像这样:

Option Explicit
Public Sub HandleErrorClicked(ByVal row As Long, ByVal col As Long)
End Sub

...并使表单类实现此接口(interface):

Implements IErrorView

Private Sub IErrorView_HandleErrorClicked(ByVal row As Long, ByVal col As Long)
    ' there's the handler!
End Sub

现在，自定义控件类可以与实现 IErrorView 接口(interface)的任何表单一起使用，并且我们可以获得编译时验证:

Private Sub lblDescription_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Dim parentForm As IErrorView
    Set parentForm = lblDescription.Parent
    parentForm.HandleErrorClicked(row, col)
End Sub