将代码放入用户表单而不是模块中是否有缺点? [英] Are there disadvantages in putting code into Userforms instead of modules?

问题描述

将代码放入 VBA 用户窗体而不是普通"模块是否有缺点?

这可能是一个简单的问题，但我在搜索网络和 stackoverflow 时还没有找到决定性的答案.

背景:我正在 Excel-VBA 中开发数据库的前端应用程序.要选择不同的过滤器，我有不同的用户表单.我问什么通用程序设计更好:(1) 将控制结构放入单独的模块 OR (2) 将下一个用户表单或操作的代码放入用户表单.

让我们举个例子.我有一个 Active-X 按钮，可以触发我的过滤器和表单.

变体 1:模块

在命令按钮中:

私有子命令Button1_Click()调用 UserInterfaceControlModule结束子

在模块中:

Sub UserInterfaceControllModule()Dim decisionInput1 As BooleanDim decisionInput2 As BooleanUserForm1.ShowDecisionInput1 = UserForm1.decision如果decisionInput1 那么UserForm2.Show别的UserForm3.Show万一结束子

在变体 1 中，控制结构位于普通模块中.关于接下来显示哪个用户表单的决定与用户表单分开.决定接下来显示哪个用户表单所需的任何信息都必须从用户表单中提取.

变体2:用户表单

在命令按钮中:

私有子命令Button1_Click()UserForm1.Show结束子

在用户表单 1 中:

Private Sub ToUserform2_Click()UserForm2.Show用户窗体1.隐藏结束子私有子 UserForm_Click()UserForm2.Show用户窗体1.隐藏结束子

在变体 2 中，控制结构直接在用户表单中，每个用户表单都有关于它后面的内容的说明.

我已经开始使用方法 2 进行开发.如果这是一个错误并且这种方法有一些严重的缺点，我想早点知道.

解决方案

免责声明我写了文章 Victor K 链接到.我拥有那个博客，并管理它的开源 VBIDE 插件项目.

您的选择都不是理想的.回归基础.

<小时><块引用>

要选择不同的过滤器，我有不同的(原文如此)用户表单.

您的规范要求用​​户需要能够选择不同的过滤器，并且您选择使用 UserForm 为其实现 UI.到目前为止，一切都很好……而且一切都在走下坡路.

让表单负责表示问题以外的任何事情是一个常见的错误，它有一个名字:它是智能 UI [反]模式，以及它的问题在于它不能扩展.它非常适合原型设计(即制作一个有效"的快速东西 - 请注意引号)，而不是需要多年维护的任何东西.

您可能已经看过这些表单，其中包含 160 个控件、217 个事件处理程序和 3 个私有程序，每个表单都包含 2000 行代码:这就是智能 UI 的可扩展性，而且它是唯一的沿着这条路走下去的可能结果.

你看，UserForm 是一个类模块:它定义了一个对象的蓝图.对象通常希望被实例化，但后来有人有一个天才的想法，即为 MSForms.UserForm 的所有实例授予一个 预先声明的 ID，在 COM 中条款意味着您基本上可以免费获得一个全局对象.

太好了！不?号

<块引用>

UserForm1.ShowDecisionInput1 = UserForm1.decision如果decisionInput1 那么UserForm2.Show别的UserForm3.Show万一

如果 UserForm1 是X'd-out"会怎样?或者如果 UserForm1 是 Unload ed?如果表单未处理其 QueryClose 事件，则该对象将被销毁 - 但因为这是 默认实例，VBA 会自动/静默地为您创建一个新实例，就在此之前您的代码读取 UserForm1.decision - 因此，您将获得 UserForm1.decision 的初始全局状态.

如果它不是默认实例，并且 QueryClose 没有被处理，那么访问被销毁对象的 .decision 成员会为您提供典型的运行时错误 91，用于访问空对象引用.

UserForm2.Show 和 UserForm3.Show 都做同样的事情:即发即忘 - 无论发生什么，并准确找出其中的组成，您需要在表单各自的代码隐藏中挖掘它.

换句话说，表格正在运行.他们负责收集数据、展示这些数据、收集用户输入，以及做任何需要用它完成的工作.这就是为什么它被称为智能 UI":UI 无所不知.

有更好的方法.MSForms 是 .NET 的 WinForms UI 框架的 COM 祖先，其祖先与其 .NET 继承者的共同点是，它与著名的 Model-View-Presenter (MVP) 模式配合得特别好.

<小时>

模型

这是您的数据.本质上，它是您的应用程序逻辑需要知道的表单之外的内容.

• UserForm1.decision 让我们继续吧.

添加一个新类，将其命名为 FilterModel.应该是一个非常简单的类:

选项显式私有类型 TModelSelectedFilter 作为字符串结束类型将此作为 TModel 私有公共属性 Get SelectedFilter() As StringSelectedFilter = this.SelectedFilter最终财产公共属性 Let SelectedFilter(ByVal value As String)this.SelectedFilter = 值最终财产公共函数 IsValid() 作为布尔值IsValid = this.SelectedFilter <>空字符串结束函数

这就是我们真正需要的:一个封装表单数据的类.该类可以负责一些验证逻辑，或者其他什么——但它不会收集数据，它不会呈现给用户，它也不会t 消费它要么.它是数据.

这里只有 1 个属性，但您可以有更多:想想表单上的一个字段 => 一个属性.

模型也是表单需要从应用程序逻辑中知道的.例如，如果表单需要一个下拉菜单来显示许多可能的选择，那么模型就是展示它们的对象.

<小时>

景观

那是你的表格.它负责了解控件、写入和读取模型，……仅此而已.我们在这里查看一个对话框:我们调出它，用户填写它，关闭它，然后程序对其进行操作——表单本身不会对它收集的数据做任何.模型可能会验证它，表单可能会决定禁用它的 Ok 按钮，直到模型说它的数据有效并且可以使用，但是在任何情况下UserForm 从工作表、数据库、文件、URL 或任何东西中读取或写入.

表单的代码隐藏非常简单:它将 UI 与模型实例连接起来，并根据需要启用/禁用其按钮.

要记住的重要事项:

• 隐藏，不要Unload:视图是一个对象，对象不会自毁.
• 从不引用表单的默认实例.
• 再次处理QueryClose，以避免自毁对象(否则表单的X-ing out"会破坏实例).

在这种情况下，代码隐藏可能如下所示:

选项显式私有类型 TView模型作为过滤器模型IsCancelled 为布尔值结束类型将此作为 TView 私有公共属性 Get Model() 作为 FilterModel设置模型 = this.Model最终财产公共属性集模型(ByVal value As FilterModel)设置 this.Model = 值证实最终财产公共属性 Get IsCancelled() As BooleanIsCancelled = this.IsCancelled最终财产私有子 TextBox1_Change()this.Model.SelectedFilter = TextBox1.Text证实结束子私有子 OkButton_Click()我.隐藏结束子私有子验证()OkButton.Enabled = this.Model.IsValid结束子私有子 CancelButton_Click()取消结束子Private Sub UserForm_QueryClose(取消为整数，关闭模式为整数)如果 CloseMode = VbQueryClose.vbFormControlMenu 那么取消 = 真取消万一结束子私有子 OnCancel()this.IsCancelled = True我.隐藏结束子

这就是表单的全部功能.它不负责了解数据的来源或如何处理数据.

<小时>

主持人

那是连接点的胶水"对象.

选项显式公共子 DoSomething()Dim 作为过滤器模型设置 m = 新过滤器模型使用新的 FilterFormSet .Model = m '设置模型.Show '显示对话框如果不是 .IsCancelled 那么'它是如何关闭的?'消费数据Debug.Print m.SelectedFilter万一结束于结束子

如果模型中的数据需要来自数据库或某个工作表，则它使用负责执行此操作的类实例(是的，另一个对象！).

调用代码可以是您的 ActiveX 按钮的点击处理程序，New - 调用演示者并调用其 DoSomething 方法.

<小时>

这不是 VBA 中关于 OOP 的全部知识(我什至没有提到接口、多态性、测试存根和单元测试)，但是如果你想要客观可扩展的代码，你会想要深入了解MVP 兔子洞，探索真正面向对象的代码为 VBA 带来的可能性.

<小时>

TL;DR:

代码(业务逻辑")根本不属于表单的代码隐藏，不属于任何需要扩展和维护数年的代码库.

在变体 1"中，代码​​很难遵循，因为您在模块之间跳转，并且表示问题与应用程序逻辑混合在一起:知道显示给定按钮 A 或按钮 B 的其他表单不是表单的工作被按下.相反，它应该让 presenter 知道用户想要做什么，并采取相应的行动.

在变体 2"中，代码​​很难遵循，因为所有内容都隐藏在用户表单的代码隐藏中:除非我们深入研究该代码，否则我们不知道应用程序逻辑是什么，现在有意em> 混合了表示和业务逻辑问题.这就是正是智能用户界面"反模式所做的.

换句话说，变体 1 比变体 2 稍好，因为至少逻辑不在代码隐藏中，但它仍然是智能 UI"，因为它运行节目告诉调用者发生了什么.

在这两种情况下，针对表单的默认实例进行编码都是有害的，因为它将状态置于全局范围内(任何人都可以从代码中的任何位置访问默认实例并对它的状态执行任何操作).

像对待对象一样对待表单:实例化它们！

在这两种情况下，由于表单的代码与应用程序逻辑紧密耦合并与表示问题交织在一起，因此完全不可能编写一个单元测试来涵盖正在发生的事情的一个方面.使用 MVP 模式，您可以完全解耦组件，将它们抽象到接口后面，隔离职责，并编写数十个自动化单元测试，涵盖每一个功能并准确记录规范内容 - 无需编写任何文档:代码成为自己的文档.

Are there disadvantages in putting code into a VBA Userform instead of into a "normal" module?

This might be a simple question but I have not found a conclusive answer to it while searching the web and stackoverflow.

Background: I am developing a Front-End Application of a database in Excel-VBA. To select different filters I have different userforms. I ask what general program design is better: (1) putting the control structure into a separate module OR (2) putting the code for the next userform or action in the userform.

Lets make an example. I have a Active-X Button which triggers my filters and my forms.

Variant1: Modules

In the CommandButton:

Private Sub CommandButton1_Click()
  call UserInterfaceControlModule
End Sub

In the Module:

Sub UserInterfaceControllModule()
Dim decisionInput1 As Boolean
Dim decisionInput2 As Boolean

UserForm1.Show
decisionInput1 = UserForm1.decision

If decisionInput1 Then
  UserForm2.Show
Else
  UserForm3.Show
End If

End Sub

In Variant 1 the control structure is in a normal module. And decisions about which userform to show next are separated from the userform. Any information needed to decide about which userform to show next has to be pulled from the userform.

Variant2: Userform

In the CommadButton:

Private Sub CommandButton1_Click()
  UserForm1.Show
End Sub

In Userform1:

Private Sub ToUserform2_Click()
  UserForm2.Show
  UserForm1.Hide
End Sub

Private Sub UserForm_Click()
  UserForm2.Show
  UserForm1.Hide
End Sub

In Variant 2 the control structure is directly in the userforms and each userform has the instructions about what comes after it.

I have started development using method 2. If this was a mistake and there are some serious drawbacks to this method I want to know it rather sooner than later.

解决方案

Disclaimer I wrote the article Victor K linked to. I own that blog, and manage the open-source VBIDE add-in project it's for.

Neither of your alternatives are ideal. Back to basics.

---

To select different filters I have differnt (sic) userforms.

Your specifications demand that the user needs to be able to select different filters, and you chose to implement a UI for it using a UserForm. So far, so good... and it's all downhill from there.

Making the form responsible for anything other than presentation concerns is a common mistake, and it has a name: it's the Smart UI [anti-]pattern, and the problem with it is that it doesn't scale. It's great for prototyping (i.e. make a quick thing that "works" - note the scare quotes), not so much for anything that needs to be maintained over years.

You've probably seen these forms, with 160 controls, 217 event handlers, and 3 private procedures closing in on 2000 lines of code each: that's how badly Smart UI scales, and it's the only possible outcome down that road.

You see, a UserForm is a class module: it defines the blueprint of an object. Objects usually want to be instantiated, but then someone had the genius idea of granting all instances of MSForms.UserForm a predeclared ID, which in COM terms means you basically get a global object for free.

Great! No? No.

UserForm1.Show
decisionInput1 = UserForm1.decision

If decisionInput1 Then
  UserForm2.Show
Else
  UserForm3.Show
End If

What happens if UserForm1 is "X'd-out"? Or if UserForm1 is Unloaded? If the form isn't handling its QueryClose event, the object is destroyed - but because that's the default instance, VBA automatically/silently creates a new one for you, just before your code reads UserForm1.decision - as a result you get whatever the initial global state is for UserForm1.decision.

If it wasn't a default instance, and QueryClose wasn't handled, then accessing the .decision member of a destroyed object would give you the classic run-time error 91 for accessing a null object reference.

UserForm2.Show and UserForm3.Show both do the same thing: fire-and-forget - whatever happens happens, and to find out exactly what that consists of, you need to dig it up in the forms' respective code-behind.

In other words, the forms are running the show. They're responsible for collecting the data, presenting that data, collecting user input, and doing whatever work needs to be done with it. That's why it's called "Smart UI": the UI knows everything.

There's a better way. MSForms is the COM ancestor of .NET's WinForms UI framework, and what the ancestor has in common with its .NET successor, is that it works particularly well with the famous Model-View-Presenter (MVP) pattern.

---

The Model

That's your data. Essentially, it's what your application logic need to know out of the form.

• UserForm1.decision let's go with that.

Add a new class, call it, say, FilterModel. Should be a very simple class:

Option Explicit

Private Type TModel
    SelectedFilter As String
End Type
Private this As TModel

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

Public Property Let SelectedFilter(ByVal value As String)
    this.SelectedFilter = value
End Property

Public Function IsValid() As Boolean
    IsValid = this.SelectedFilter <> vbNullString
End Function

That's really all we need: a class to encapsulate the form's data. The class can be responsible for some validation logic, or whatever - but it doesn't collect the data, it doesn't present it to the user, and it doesn't consume it either. It is the data.

Here there's only 1 property, but you could have many more: think one field on the form => one property.

The model is also what the form needs to know from the application logic. For example if the form needs a drop-down that displays a number of possible selections, the model would be the object exposing them.

---

The View

That's your form. It's responsible for knowing about controls, writing to and reading from the model, and... that's all. We're looking at a dialog here: we bring it up, user fills it up, closes it, and the program acts upon it - the form itself doesn't do anything with the data it collects. The model might validate it, the form might decide to disable its Ok button until the model says its data is valid and good to go, but under no circumstances a UserForm reads or writes from a worksheet, a database, a file, a URL, or anything.

The form's code-behind is dead simple: it wires up the UI with the model instance, and enables/disables its buttons as needed.

The important things to remember:

• Hide, don't Unload: the view is an object, and objects don't self-destruct.
• NEVER refer to the form's default instance.
• Always handle QueryClose, again, to avoid a self-destructing object ("X-ing out" of the form would otherwise destroy the instance).

In this case the code-behind might look like this:

Option Explicit
Private Type TView
    Model As FilterModel
    IsCancelled As Boolean
End Type
Private this As TView

Public Property Get Model() As FilterModel
    Set Model = this.Model
End Property

Public Property Set Model(ByVal value As FilterModel)
    Set this.Model = value
    Validate
End Property

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

Private Sub TextBox1_Change()
    this.Model.SelectedFilter = TextBox1.Text
    Validate
End Sub

Private Sub OkButton_Click()
    Me.Hide
End Sub

Private Sub Validate()
    OkButton.Enabled = this.Model.IsValid
End Sub

Private Sub CancelButton_Click()
    OnCancel
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = VbQueryClose.vbFormControlMenu Then
        Cancel = True
        OnCancel
    End If
End Sub

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

That's literally all the form does. It isn't responsible for knowing where the data comes from or what to do with it.

---

The Presenter

That's the "glue" object that connects the dots.

Option Explicit

Public Sub DoSomething()
    Dim m As FilterModel
    Set m = New FilterModel
    With New FilterForm
        Set .Model = m 'set the model
        .Show 'display the dialog
        If Not .IsCancelled Then 'how was it closed?
            'consume the data
            Debug.Print m.SelectedFilter
        End If
    End With
End Sub

If the data in the model needed to come from a database, or some worksheet, it uses a class instance (yes, another object!) that's responsible for doing just that.

The calling code could be your ActiveX button's click handler, New-ing up the presenter and calling its DoSomething method.

---

This isn't everything there is to know about OOP in VBA (I didn't even mention interfaces, polymorphism, test stubs and unit testing), but if you want objectively scalable code, you'll want to go down the MVP rabbit hole and explore the possibilities truly object-oriented code bring to VBA.

---

TL;DR:

Code ("business logic") simply doesn't belong in forms' code-behind, in any code base that means to scale and be maintained across several years.

In "variant 1" the code is hard to follow because you're jumping between modules and the presentation concerns are mixed with the application logic: it's not the form's job to know what other form to show given button A or button B was pressed. Instead it should let the presenter know what the user means to do, and act accordingly.

In "variant 2" the code is hard to follow because everything is hidden in userforms' code-behind: we don't know what the application logic is unless we dig into that code, which now purposely mixes presentation and business logic concerns. That is exactly what the "Smart UI" anti-pattern does.

In other words variant 1 is slightly better than variant 2, because at least the logic isn't in the code-behind, but it's still a "Smart UI" because it's running the show instead of telling its caller what's happening.

In both cases, coding against the forms' default instances is harmful, because it puts state in global scope (anyone can access the default instances and do anything to its state, from anywhere in the code).

Treat forms like the objects they are: instantiate them!

In both cases, because the form's code is tightly coupled with the application logic and intertwined with presentation concerns, it's completely impossible to write a single unit test that covers even one single aspect of what's going on. With the MVP pattern, you can completely decouple the components, abstract them behind interfaces, isolate responsibilities, and write dozens of automated unit tests that cover every single piece of functionality and document exactly what the specifications are - without writing a single bit of documentation: the code becomes its own documentation.

这篇关于将代码放入用户表单而不是模块中是否有缺点?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！