excel - 子函数显示用户窗体

Question

我有一个具有多个UserForms的Excel文件。要打开一个用户窗体，我有如下代码

Sub runAdjuster()
   Adjuster.Show
End Sub

其中大约有5个。就应将此代码保存在何处而言，什么是最佳实践？我最初在模块中拥有它，但是决定将其移至ThisWorkbook对象。寻找有关通常可以保持代码清洁的提示。

Answer 1

假设Adjuster是表单的名称，那么您在此处使用默认实例，这不是理想的选择。

这样会更好：

Dim view As Adjuster
Set view = New Adjuster
view.Show

是的，这是更多代码。但是您使用的是专用对象（即 view），并且如果该对象的状态被修改，这些更改将不会影响默认实例。可以将默认实例视为一个全局对象：它是全局的，不是非常面向对象的。

现在，您可能会争论，为什么不与声明在同一行上“更新”对象呢？

考虑一下：

Sub DoSomething()
    Dim c As New Collection
    Set c = Nothing
    c.Add "test"
End Sub

这段代码是否访问空引用并出现运行时错误91？没有！令人困惑？是!因此，请避免使用 As New快捷方式，除非您希望VBA自动地在背后进行隐式操作。



因此，您正在询问最佳实践...我倾向于将VBA UserForms视为 winforms的早期.NET早期版本，而WinForms的最佳实践设计模式是Model-View-Presenter模式（也称为“ MVP” ”）。

按照这种模式，您将使UserForms严格负责演示，并且将在演示者对象中或在演示者使用的专用对象中实现业务逻辑。像这样：

类模块：MyPresenter

Presenter类从模型接收事件，并根据模型的状态执行应用程序逻辑。它知道视图的概念，但不必与具体实现紧密结合（例如 MyUserForm）-使用适当的工具，您可以 write unit tests以编程方式验证逻辑，而无需实际运行代码并显示该表单，然后单击任意位置。

Option Explicit

Private Type TPresenter
    View As IView
End type

Public Enum PresenterError
    ERR_ModelNotSet = vbObjectError + 42
End Enum

Private WithEvents viewModel As MyModel
Private this As TPresenter

Public Sub Show()
    If viewModel Is Nothing Then
        Err.Raise ERR_ModelNotSet, "MyPresenter.Show", "Model is not set to an object reference."
    End If
    'todo: set up model properties
    view.Show
    If Not view.IsCancelled Then DoSomething
End Sub

Public Property Get View() As IView
    Set View = this.View
End Property

Public Property Set View(ByVal value As IView)
    Set this.View = value
    If Not this.View Is Nothing Then Set this.View.Model = viewModel
End Property

Public Property Get Model() As MyModel
    Set Model = viewModel
End Property

Public Property Set Model(ByVal value As MyModel)
    Set viewModel = value
    If Not this.View Is Nothing Then Set this.View.Model = viewModel        
End Property

Private Sub Class_Terminate()
    Set this.View.Model = Nothing
    Set this.View = Nothing
    Set viewModel = Nothing
End Sub

Private Sub viewModel_PropertyChanged(ByVal changedProperty As ModelProperties)
    'todo: execute logic that needs to run when something changes in the form
End Sub

Private Sub DoSomething()
    'todo: whatever needs to happen after the form closes
End Sub

类模块：IView

那是代表视图概念的抽象，它暴露了演示者需要了解的任何用户窗体的所有内容-请注意，它需要了解的所有内容并不多：

Option Explicit

Public Property Get Model() As Object
End Property

Public Property Set Model(ByVal value As Object)
End Property

Public Property Get IsCancelled() As Boolean
End Property

Public Sub Show()
End Sub

类模块：MyModel

模型类封装表单需要和处理的数据。它既不了解视图，也不了解演示者：它只是封装数据的容器，具有简单的逻辑，当修改任何属性时，逻辑使视图和演示者都能执行代码。

Option Explicit

Private Type TModel
    MyProperty As String
    SomeOtherProperty As String
    'todo: wrap members here
End Type

Public Enum ModelProperties
    MyProperty
    SomeOtherProperty
    'todo: add enum values here for each monitored property
End Enum

Public Event PropertyChanged(ByVal changedProperty As ModelProperties)
Private this As TModel

Public Property Get MyProperty() As String
    MyProperty = this.MyProperty
End Property

Public Property Let MyProperty(ByVal value As String)
    If this.MyProperty <> value Then
        this.MyProperty = value
        RaiseEvent PropertyChanged(MyProperty)
    End If
End Property

Public Property Get SomeOtherProperty() As String
    SomeProperty = this.SomeOtherProperty
End Property

Public Property Let SomeOtherProperty(ByVal value As String)
    If this.SomeOtherProperty <> value Then
        this.SomeOtherProperty = value
        RaiseEvent PropertyChanged(SomeOtherProperty)
    End If
End Property

'todo: expose other model properties

用户窗体：MyUserForm

UserForm严格负责视觉呈现；它所有的事件处理程序都需要更改模型中属性的值-然后模型告诉演示者“嘿，我已经被修改了！”，演示者便相应地采取了行动。该表单还侦听模型上的修改属性，因此当演示者更改模型时，视图可以执行代码并相应地更新自身。这是一个简单形式的示例，该形式将 MyProperty模型属性“绑定”到某些 TextBox1的文本。我为 SomeOtherProperty添加了一个侦听器，只是为了说明在模型更改时也可以间接更新视图。

显然，视图不会对与演示者相同的属性做出反应，否则，您将输入无尽的回调乒乓球，最终会炸毁堆栈...但是您明白了。

请注意，该表单实现了 IView接口，以便演示者可以与之交谈，而无需实际了解其内部工作原理。接口实现只是指具体成员，但是具体成员甚至不需要实际存在，因为它们甚至都不会被使用！

Option Explicit
Implements IView

Private Type TView
    IsCancelled As Boolean
End Type

Private WithEvents viewModel As MyModel
Private this As TView

Private Property Get IView_Model() As Object
    Set IView_Model = Model
End Property

Private Property Set IView_Model(ByVal value As Object)
    Set Model = value
End Property

Private Property Get IView_IsCancelled() As Boolean
    IView_IsCancelled = IsCancelled
End Property

Private Sub IView_Show()
    Show vbModal
End Sub

Public Property Get Model() As MyModel
    Set Model = viewModel
End Property

Public Property Set Model(ByVal value As MyModel)
    Set viewModel = value
End Property

Public Property Get IsCancelled() As Boolean
    IsCancelled = this.IsCancelled
End Property

Private Sub CancelButton_Click()
    this.IsCancelled = True
    Me.Hide
End Sub

Private Sub OkButton_Click()
    Me.Hide
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    '"x-ing out" of the form is like clicking the Cancel button
    If CloseMode = VbQueryClose.vbFormControlMenu Then
        this.IsCancelled = True
    End If
End Sub

Private Sub UserForm_Activate()
    If viewModel Is Nothing Then
        MsgBox "Model property must be assigned before the view can be displayed.", vbCritical, "Error"
        Unload Me
    Else
        Me.TextBox1.Text = viewModel.MyProperty
        Me.TextBox1.SetFocus
    End If
End Sub

Private Sub TextBox1_Change()
    'UI elements update the model properties
    viewModel.MyProperty = Me.TextBox1.Text
End Sub

Private Sub viewModel_PropertyChanged(ByVal changedProperty As ModelProperties)
    If changedProperty = SomeOtherProperty Then
        Frame1.Caption = SomeOtherProperty
    End If
End Sub

模块：宏

假设您的电子表格具有形状，并且您希望在单击它时运行该逻辑。您需要在该形状上附加一个宏-我想在名为“ Macros”的标准模块（.bas）中重新组合所有宏，该模块只包含看起来像这样的公共过程：

Option Explicit

Public Sub DoSomething()

    Dim presenter As MyPresenter 
    Set presenter = New MyPresenter

    Dim theModel As MyModel
    Set theModel = New MyModel

    Dim theView As IView
    Set theView = New MyUserForm

    Set presenter.Model = theModel
    Set presenter.View = theView
    presenter.Show

End Sub

现在，如果您想以编程方式测试演示者逻辑而不显示表单，那么您要做的就是实现一个“假”视图，并编写一个可以满足您需要的测试方法：

类：MyFakeView

Option Explicit
Implements IView

Private Type TFakeView
    IsCancelled As Boolean
End Type

Private this As TFakeView

Private Property Get IView_Model() As Object
    Set IView_Model = Model
End Property

Private Property Set IView_Model(ByVal value As Object)
    Set Model = value
End Property

Private Property Get IView_IsCancelled() As Boolean
    IView_IsCancelled = IsCancelled
End Property

Private Sub IView_Show()
    IsCancelled = False
End Sub

Public Property Get IsCancelled() As Boolean
    IsCancelled = this.IsCancelled
End Property

Public Property Let IsCancelled(ByVal value As Boolean)
    this.IsCancelled = value
End Property

模块：TestModule1

可能还有其他工具，但是由于我实际上是写过这一工具的，所以我喜欢它的工作原理，而无需大量样板设置代码或包含可执行指令的注释，因此，我强烈建议使用Rubberduck单元测试。 [非常简单]的测试模块如下所示：

'@TestModule
Option Explicit
Option Private Module
Private Assert As New Rubberduck.AssertClass

'@TestMethod
Public Sub Model_SomePropertyInitializesEmpty()
    On Error GoTo TestFail

    'Arrange
    Dim presenter As MyPresenter 
    Set presenter = New MyPresenter

    Dim theModel As MyModel
    Set theModel = New MyModel

    Set presenter.Model = theModel
    Set presenter.View = New MyFakeView

    'Act
    presenter.Show

    'Assert
    Assert.IsTrue theModel.SomeProperty = vbNullString

TestExit:
    Exit Sub
TestFail:
    Assert.Fail "Test raised an error: #" & Err.Number & " - " & Err.Description
End Sub

Rubberduck单元测试使您可以使用此分离的代码来测试要测试的有关应用程序逻辑的所有内容-只要保持该应用程序逻辑的分离和编写可测试的代码，就可以使用单元测试来记录如何VBA应用程序应该表现得很好，可以测试说明规格的文件-就像使用C＃或Java一样，或者可以用其编写单元测试的任何其他OOP语言。

关键是，VBA也可以做到。



过度杀伤力？要看。规格一直在变化，代码也相应地变化。由于 the Project Explorer doesn't drill down to module members，在电子表格的后台代码中实现所有应用程序逻辑变得非常烦人，因此在何处查找实现的内容很容易变得烦人。

当逻辑在表单的代码隐藏中实现，然后又有 Button_Click处理程序进行数据库调用或电子表格操作时，情况甚至更糟。

在责任越少的对象中实现的代码，使代码可重用，并且易于维护。

您对“具有多个用户表单的Excel文件”的含义不完全准确，但是如果需要，您可以有一个“主要”演示者类，该类接收4-5个“儿童”演示者，每个负责人与每个“子”形式相关的特定逻辑。

就是说，如果您有要重构的工作代码（可以按预期工作），并且可以提高效率，或更易于阅读/维护，则可以将其发布在 Code Review Stack Exchange上，这就是该网站的目的。




免责声明： I maintain the Rubberduck project.