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

问题描述

将代码放入VBA用户表单而不是常规"模块是否有缺点?

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

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

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

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.

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

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

变体1:模块

在命令按钮中:

Private Sub CommandButton1_Click()
  call UserInterfaceControlModule
End Sub

在模块中:

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

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

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:用户表单

在CommadButton中:

In the CommadButton:

Private Sub CommandButton1_Click()
  UserForm1.Show
End Sub

在Userform1中:

In Userform1:

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

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

在变体2中，控制结构直接位于用户窗体中，每个用户窗体都有有关后续操作的说明.

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

我已经开始使用方法2进行开发.如果这是一个错误，并且此方法有一些严重的缺点，我希望尽快了解它.

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.

推荐答案

免责声明我写了文章 Victor K 链接到.我拥有该博客，并管理该博客的开源VBIDE加载项项目.

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.

要选择不同的过滤器，我有不同的(sic)用户格式.

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

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.

让表单负责除表示问题之外的其他任何事情，这是一个常见错误，并且有一个名称:这是 Smart UI [anti-]模式，它的问题是无法扩展.这对于原型制作非常有用(例如，使一件快速的事情起作用"，请注意吓人的引号)，而不是需要多年维护的东西.

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.

您可能已经看到了这些表单，其中有160个控件，217个事件处理程序和3个私有过程分别封闭在2000行代码中:这就是 Smart UI 扩展的糟糕程度，并且这是唯一的可能的结果.

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.

您会看到，UserForm是一个类模块:它定义了对象的蓝图.对象通常要被实例化，但后来有人有了天才的想法，即授予MSForms.UserForm的所有实例一个预先声明的ID ，用COM术语来说，这意味着您基本上可以得到一个全局对象.免费对象.

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.

太好了！不?不.

UserForm1.Show
decisionInput1 = UserForm1.decision

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

如果UserForm1为"X'd-out"会怎样?还是UserForm1被Unload了?如果表单未处理其QueryClose事件，则该对象将被销毁-但由于这是默认实例，因此在代码读取-这样一来，您将获得UserForm1.decision的初始全局状态.

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.

如果它不是默认实例，并且未处理QueryClose，则访问已破坏对象的.decision成员将为您提供经典的运行时错误91访问空对象引用.

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和UserForm3.Show都执行相同的操作:随便忘了-无论发生什么情况，并准确找出其组成，您需要在表单的各自代码中进行挖掘

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.

有一个更好的方法. MSForms是.NET WinForms UI框架的COM祖先，而祖先与其.NET继承者的共同点在于，它可以与著名的 Model-View-Presenter (MVP)模式一起很好地工作

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.

这是您的数据.本质上，这是表单中您的应用程序逻辑需要了解的内容.

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

• UserForm1.decision让我们开始吧.

• UserForm1.decision let's go with that.

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

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.

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

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.

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

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.

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

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

重要的事情要记住:

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

• 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.

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

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.

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

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

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

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.

代码(业务逻辑")根本不属于表单的代码背后的任何代码库，这些代码库意味着可以扩展和维护数年.

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.

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

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.

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

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.

换句话说，变体1比变体2稍微好一点，因为至少逻辑不在后台代码中，但是它仍然是智能用户界面"，因为它代替了运行节目告诉呼叫者发生了什么事.

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!

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

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.

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