excel - 功能化事件驱动的无模式用户窗体类

Question

我一直在移动我的一些代码以利用 UserForms利用 Modeless因此用户可以从工作表中复制数据。我终于"generalized" this answer并让它工作。但是，我想避免为每个 UserForm 创建一个类，而事实是我不是 100% 了解代码中发生了什么。有没有一种方法可以轻松迁移我的所有用户表单以利用“事件驱动的无模式用户表单”的以下功能。基本上，我想将 UserForm 作为变量传递，并在类中有一堆 subs，主要是通用的和特定的，或者可能只是使用 If/Then在 _Closed 之后调用正确的退出模块事件。
希望这是有道理的，如果您需要进一步澄清，请告诉我。
通用代码:
模块名称无关紧要
用户窗体名称 = UserForm1类名 = Class1 模块代码:

Private UserFormNameStr As Class1
Public Sub DoStuff()
    Set UserFormNameStr = New Class1
    UserFormNameStr.ClassSubNameStrSubName
End Sub
Public Sub CallMeWhenUserFormClosed()
 Debug.Print "Module Code Run"
End Sub

类(class)代码:

Private WithEvents UserFormNameStr As UserForm1
Private Sub Class_Initialize()
    Set UserFormNameStr = New UserForm1
End Sub
Public Sub ClassSubNameStrSubName()
    UserFormNameStr.Show vbModeless
End Sub
Private Sub UserFormNameStr_Closed()
    '_Closed is required syntax
    Debug.Print "Closed Event"
    Call CallMeWhenUserFormClosed
End Sub

用户表单代码:

Public Event Closed()
Private Sub UserForm_Initialize()
    '
End Sub
Sub OkButton_Click()
 Debug.Print "Raising Events from OK Button!"
 RaiseEvent Closed
 Unload Me
End Sub
Private Sub CancelButton_Click()
 Unload Me
 End
End Sub

更新:
也许我正在 Workbook_Open 上寻找带有“钩子(Hook)”的东西？

Answer 1

WithEvents 使这一切顺利进行的原因是宣言。使用 WithEvents 声明的实例变量修饰符将出现在编辑器的左侧下拉列表中。
要为事件提供程序创建事件处理程序，请从左侧下拉列表中选择变量，然后在右侧下拉列表中选择要处理的事件。
最终模块看起来像这样，即带有 WithEvents您要处理以下事件的每个无模式表单的声明:

Private WithEvents UserFormNameStr As UserForm1
Private WithEvents SomeOtherUserForm As UserForm2
Private WithEvents AnotherUserForm As UserForm3

Private Sub Class_Initialize()
    Set UserFormNameStr = New UserForm1
    Set SomeOtherUserForm = New UserForm2
    Set AnotherUserForm = New UserForm3
End Sub

Public Sub ClassSubNameStrSubName() 'weird name, consider methods that begin with a verb
    UserFormNameStr.Show vbModeless
End Sub

Public Sub ShowSomeOtherForm()
    SomeOtherUserForm.Show vbModeless
End Sub

Public Sub ShowAnotherForm()
    AnotherUserForm.Show vbModeless
End Sub

Private Sub UserFormNameStr_Closed() 'select "UserFormNameStr" from the left-side dropdown: NEVER hand-write event handler signatures.
    Debug.Print "Closed Event (UserFormNameStr)"
    CallMeWhenUserFormClosed
End Sub

Private Sub SomeOtherUserForm_Closed() 'select "SomeOtherUserForm" from the left-side dropdown: NEVER hand-write event handler signatures.
    Debug.Print "Closed Event (SomeOtherUserForm)"
    CallMeWhenSomeOtherUserFormClosed
End Sub

Private Sub AnotherUserForm_Closed() 'select "AnotherUserForm" from the left-side dropdown: NEVER hand-write event handler signatures.
    Debug.Print "Closed Event (AnotherUserForm)"
    CallMeWhenAnotherUserFormClosed
End Sub

一个处理程序，多种形式？
如果全部 _Closed处理程序需要做完全相同的事情，然后我们可以涉及接口(interface)和多态性，并在 3 个实例中存在一个类，每个实例为各自的形式做自己的事情 - 但 VBA 不公开 Public Event类的默认接口(interface)上的声明，所以这里的范例有点不同，因为它不涉及 Event和 WithEvents ，这样也可以说更简单。
定义 IHandleClosingForm接口(interface):向您的项目添加一个新的类模块，但不关注实现 - 只是您想要的功能的非常高级的抽象(这里是 Rubberduck annotations ):

'@ModuleDescription "An object that handles a form being closed."
'@Interface
'@Exposed
Option Explicit

'@Description "A callback invoked when a form is closed."
Public Sub Closing(ByVal Form As UserForm)
End Sub

在每个表单模块中，持有对该接口(interface)的引用，并调用它的 Closing表格中的方法 QueryClose处理程序:

Option Explicit
Public CloseHandler As IHandleClosingForm

`...

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = VbQueryClose.vbFormControlMenu Then
        'user clicked the [X] button, form instance is going to be destroyed!
        Cancel = True 'prevents a self-destructing form instance.
        Me.Hide
    End If
    'don't assume the caller set the CloseHandler:
    If Not CloseHandler Is Nothing Then CloseHandler.Closing(Me)
    '...
End Sub

现在在演示者类中实现该接口(interface):

Option Explicit
Implements IHandleClosingForm

'...rest of the module...

Private Sub IHandleClosingForm_Closing(ByVal Form As UserForm)
'NOTE: procedure exits to the still-closing form's QueryClose handler
    If TypeOf Form Is UserForm1 Then
        CallMeWhenForm1Closes
    Else
        CallMeWhenAnyOtherFormCloses
    End If
End Sub

最后一步是通过设置公共(public) CloseHandler 在表单和演示者之间引入循环引用。显示表单之前的属性:

Set theForm.CloseHandler = Me
theForm.Show vbModeless

这会起作用，但是会出现内存泄漏，并且表单和演示者实例都不会终止(处理 Class_Terminate 以找出答案!)，您将要努力避免这种情况(Excel 将/应该在它会关闭 VBA 环境)。
解决办法是一有机会就解开这个结，所以请确保您的表格' QueryClose处理程序设置 IHandleClosingForm引用 Nothing一旦它不再有用:

'don't assume the caller set the CloseHandler:
If Not CloseHandler Is Nothing Then CloseHandler.Closing(Me)
Set CloseHandler = Nothing 'release the handler reference

下次显示表单并设置处理程序时，它将位于表单的另一个实例上。
如果您需要表单的状态在显示和关闭之间保持不变，那么您必须将状态与表单分开(并保持状态但仍然正确销毁表单对象)，...但这是另一个话题天 :)