在VBA中，应该在类模块中避免修改文档的代码 [英] In VBA should code that modifies document be avoided in a class module

问题描述

我开始在VBA中使用Classes，并欣赏一些已经在SO上提供的一些奇妙的信息。

据我所知，缺少的是解释课程中的代码应该或正如我怀疑不应该做的。例如：

说我有一个文档，希望插入/修改一个表。在这个例子中，我想：

• 检查表是否存在


• 如果表不存在：
  
  
  
  
  • 在特定位置添加表
  
  
  • 将信息添加到表（即添加行）
  
  


• 如果表存在
  
  
  
  
  • 添加/删除信息到/从表
  
  
  • 根据一些条件排序表
  
  

对于排序，我想象一个类模块将非常适合根据某些标准来确定信息应放入表格的顺序。

但理想情况：

• 模块（或第二类模块）用于检查和编辑文档？

或

• 使用常规模块最好完成检查和/或编辑？或者不重要吗？

如果有一个首选方法，那么每种方法的优点/缺点是什么？

[修改 - 后续问题在这里：了解VBA中的课程 - 帮助改进这些意见 ]

解决方案

首先，进入OOP的美妙的兔子洞穴！

简短答案：取决于。

---

（非常） ：

你应该避免从 Application.Worksheets （或 Application.Sheets ）集合，并使用该工作表的 CodeName 。 VBA创建一个全局范围的对象引用，供您使用，每个工作表的 CodeName 命名。

代码得到编译，而$ Sheet1 未在任何地方声明：

 选项显式
 
 Sub Test（）
 Debug.Print Sheet1.CodeName 
 End Sub 
  

使用该全局范围的免费对象变量，在工作表代码隐藏之外的任何地方实现工作表特定功能的问题是，单独的模块现在已经被与 Sheet1 对象。

根据工作表的类模块。 任何工作表。

您需要专注，有凝聚力的模块 - 高度凝聚力。

通过在另一个模块中编写工作表特定的代码（无论是标准还是类模块），您正在创建一个依赖和增加耦合，这降低了<可测试性 - 在 Class1 中考虑这个代码：

  Public Sub DoSomething（）
 With Sheet1 
'do stuff 
 End With 
 End Sub 
  

现在 Class1 只能曾经与 Sheet1 一起工作。这将更好：

  Public Sub DoSomething（ByVal sheet As Worksheet）
带表单
'do东西
结束
结束Sub 
  

这里发生了什么？ 依赖注入。我们在特定工作表上有一个依赖，而不是针对该特定对象进行编码，我们告诉世界给我任何工作表，我会用它来做我的事情。这是在方法级别。

如果一个类意味着使用单个特定的工作表，并展示了使用该工作表执行各种操作的多种方法在每个方法上都有一个 ByVal表作为工作表参数没有任何意义。

相反，将其注入属性：

 私有mSheet作为工作表
 
公共属性获取表（）为工作表
设置Sheet = mSheet 
结束属性
 
公共属性集表（ByVal值作为工作表）
设置mSheet =值
结束属性
  

现在，该类的所有方法都可以使用 Sheet ...唯一的问题是，消费该类的客户端代码现在需要记住 Set Sheet property，否则可能会出现错误。这是不好的设计IMO。

一个解决方案可能是进一步推动依赖注入原则，实际上取决于抽象/ em>的;我们正式化我们要为该类公开的接口，使用另一个将用作接口的类模块 - IClass1 类不实现任何东西，它只是定义存根暴露的存根：

 '@ Interface 
 Option Explicit 
 
公共属性获取表（）作为工作表
结束属性
 
公共子DoSomething（）
结束Sub 
  

我们的 Class1 类模块现在可以实现该界面，如果您一直在追踪这个远，希望我不会在这里失去你的意思：

注意：模块和成员属性在VBE中不可见。他们在这里代表了相应的 Rubberduck 注释。

 '@ PredeclaredId 
'@Exposed 
选项显式
实现IClass1 
 
私有mSheet As工作表
 
公共函数创建（ByVal pSheet As Worksheet）As IClass1 
新的Class1 
设置.Sheet = pSheet 
设置Create = .Self 
结束
结束函数
 
朋友属性Get Self（）As IClass1 
 Set Self = Me 
结束属性
 
私有属性获取IClass1_Sheet（ ）作为工作表
设置IClass1_Sheet = mSheet 
结束属性
 
私有子类IClass1_DoSomething（）
'实现到这里
 End Sub 
  

此 Class1 类模块提供两个接口：

• Class1 成员，可从 Predec laredId 实例：
  
  
  
  
  • 创建（ByVal pSheet As Worksheet）As IClass1
  
  
  • Self（）As IClass1
  
  


• IClass1 成员，可以从 IClass1 界面访问：
  
  
  
  
  • Sheet（）作为工作表
  
  
  • DoSomething（）
  
  

现在，调用代码可以如下所示：

  Dim foo As IClass1 
设置foo = Class1.Create（Sheet1）
 Debug.Assert foo.Sheet是Sheet1 
 foo.DoSomething 
  

因为它是针对 IClass1 界面，调用代码只能看到 Sheet 和 DoSomething 成员。由于 VB_PredeclaredId 属性 Class1 ，创建函数可以通过 Class1 默认实例进行访问，就像 Sheet1 无法访问创建一个实例（UserForm类也有默认实例）。

这是工厂设计模式：我们使用默认实例作为工厂，其作用是创建和初始化 IClass1 接口的实现，其中 Class1 刚刚实现。

使用 Class1 完全与 Sheet1 分离，绝对没有任何错误，让 Class1 负责任何需要发生的事情工作表被初始化。

耦合被照顾。凝聚力完全是另一个问题：如果你发现 Class1 正在增长头发和触手，并且负责这么多事情，你甚至不知道什么已经写完了，可能的是，单一责任原则正在遭受殴打，而 IClass1 接口有这么多无关的成员， em>接口隔离原理也也进行殴打，其原因可能是因为接口没有设计使用开/关原则

---

以上无法用标准模块实现。标准模块对于OOP来说不太好，这意味着更紧密的耦合，从而降低可测试性。

---

TL; DR：

没有一种单一的正确方式来设计任何。

• 如果您的代码可以处理与特定工作表紧密耦合，更喜欢在该工作表的代码隐藏中实现该工作表的功能，以获得更好的>凝聚 即可。仍然使用专门的对象（类）进行专门的任务：如果您的工作表代码隐藏负责设置数据库连接，通过网络发送参数化查询，检索结果并将其转储到工作表中，那么您是> Do it It Wrong™，现在无需单独测试该代码，而不会触碰数据库。


• 如果您的代码更复杂，无法承受紧密耦合一个特定的工作表，或者如果工作表在编译时不存在，实现可以与任何工作表一起工作的类中的功能，并且有一个负责任的类对于运行时创建的工作表的模型。

IMO标准模块只能用于公开入口点（宏，UDF，Rubberduck测试方法，以及 Option Private Module ，一些常见的实用程序函数），并且包含相当少的仅仅初始化objec的代码ts和它们的依赖关系，然后它的类一直下来。

I am starting to use Classes in VBA and appreciate some of the fantastic information that is already available on SO.

As far as I can tell, what seems to be lacking is an explanation of what the code in a class should or, as I suspect, should NOT do. For example:

Lets say I have a document and wish to insert / modify a table. In this example I'd like to:

• check if the table exists
• if table does not exist:
  • add the table at a specific location
  • add information to the table (i.e. add rows)
• if table does exist
  • add / delete information to /from the table
  • sort the table according to some criteria

With respect to 'sorting' I imagine that a class module would be well suited to determining the order that information should be put into a table based on some criteria.

But ideally:

• Should the class module (or a 2nd class module) be used to check and edit the document?

OR

• Would checking and/or editing be best done using a regular module?

Or doesn't it matter? If there is a preferred way then what are the advantages / disadvantages of each approach?

[Edit - follow-on question here: Understanding classes in VBA - help improve these comments ]

解决方案

First off, kudos for entering the wonderful rabbit hole of OOP!

Short answer: It depends.

---

(very) long answer:

You'll want to avoid pulling a worksheet [that exists at compile-time] from the Application.Worksheets (or Application.Sheets) collection, and use that sheet's CodeName instead. VBA creates a global-scope object reference for you to use, named after every worksheet's CodeName.

That's how this code gets to compile, with Sheet1 not being declared anywhere:

Option Explicit

Sub Test()
    Debug.Print Sheet1.CodeName
End Sub

The problem with implementing worksheet-specific functionality anywhere other than in that worksheet's code-behind, using that global-scope "free" object variable, is that the separate module is now coupled with that Sheet1 object.

Class module depending on a worksheet. Any worksheet.

You want focused, cohesive modules - high cohesion. And low coupling.

By writing worksheet-specific code in another module (be it a standard or a class module), you're creating a dependency and increasing coupling, which reduces testability - consider this code in Class1:

Public Sub DoSomething()
    With Sheet1
        ' do stuff
    End With
End Sub

Now Class1 can only ever work with Sheet1. This would be better:

Public Sub DoSomething(ByVal sheet As Worksheet)
    With sheet
        ' do stuff
    End With
End Sub

What happened here? Dependency Injection. We have a dependency on a specific sheet, but instead of coding against that specific object, we tell the world "give me any worksheet and I'll do my thing with it". That's at method level.

If a class means to work with a single specific worksheet, and exposes multiple methods that do various things with that worksheet, having a ByVal sheet As Worksheet parameter on every single method doesn't make much sense.

Instead you'll inject it as a property:

Private mSheet As Worksheet

Public Property Get Sheet() As Worksheet
    Set Sheet = mSheet
End Property

Public Property Set Sheet(ByVal value As Worksheet)
    Set mSheet = value
End Property

And now all methods of that class can work with Sheet... the only problem is that the client code consuming that class now needs to remember to Set that Sheet property, otherwise errors can be expected. That's bad design IMO.

One solution could be to push the Dependency Injection Principle a notch further, and actually depend on abstractions; we formalize the interface we want to expose for that class, using another class module that will act as the interface - that IClass1 class doesn't implement anything, it just defines stubs for what's exposed:

'@Interface
Option Explicit

Public Property Get Sheet() As Worksheet
End Property

Public Sub DoSomething()
End Sub

Our Class1 class module can now implement that interface, and if you've been following this far, hopefully I don't lose you here:

NOTE: Module and member attributes are not visible in the VBE. They're represented here with their corresponding Rubberduck annotations.

'@PredeclaredId
'@Exposed
Option Explicit
Implements IClass1

Private mSheet As Worksheet

Public Function Create(ByVal pSheet As Worksheet) As IClass1
    With New Class1
        Set .Sheet = pSheet
        Set Create = .Self
    End With
End Function

Friend Property Get Self() As IClass1
    Set Self = Me
End Property

Private Property Get IClass1_Sheet() As Worksheet
    Set IClass1_Sheet = mSheet
End Property

Private Sub IClass1_DoSomething()
    'implementation goes here
End Sub

This Class1 class module presents two interfaces:

• Class1 members, accessible from the PredeclaredId instance:
  • Create(ByVal pSheet As Worksheet) As IClass1
  • Self() As IClass1
• IClass1 members, accessible from the IClass1 interface:
  • Sheet() As Worksheet
  • DoSomething()

Now the calling code can look like this:

Dim foo As IClass1
Set foo = Class1.Create(Sheet1)
Debug.Assert foo.Sheet Is Sheet1
foo.DoSomething

Because it's written against the IClass1 interface, the calling code only "sees" the Sheet and DoSomething members. Because of the VB_PredeclaredId attribute of Class1, the Create function can be accessed via the Class1 default instance, pretty much exactly like Sheet1 gets accessed without creating an instance (UserForm classes have that default instance, too).

This is the factory design pattern: we're using the default instance as a factory whose role is to create and initialize an implementation of the IClass1 interface, which Class1 just so happens to be implementing.

With Class1 completely decoupled from Sheet1, there's absolutely nothing wrong with having Class1 responsible for everything that needs to happen on whatever worksheet it's initialized with.

Coupling is taken care of. Cohesion is another problem entirely: if you find Class1 is growing hair and tentacles and becomes responsible for so many things you don't even know what it was written for anymore, chances are that the Single Responsibility Principle is taking a beating, and that the IClass1 interface has so many unrelated members that the Interface Segregation Principle is also taking a beating, and the reason for that is probably because the interface wasn't designed with the Open/Closed Principle in mind.

---

The above couldn't be implemented with standard modules. Standard modules don't play quite well with OOP, which means tighter coupling and thus lower testability.

---

TL;DR:

There isn't one single "right" way to design anything.

• If your code can deal with being tightly coupled with a specific worksheet, prefer implementing the functionality for that worksheet in that worksheet's code-behind, for better cohesion. Still use specialized objects (classes) for specialized tasks: if your worksheet code-behind is responsible for setting up a database connection, sending a parameterized query over the network, retrieving the results and dumping them into the worksheet, then you're Doing It Wrong™ and now testing that code in isolation, without hitting the database, is impossible.
• If your code is more complex and can't afford tight coupling with a specific worksheet, or if the worksheet doesn't exist at compile-time, implement the functionality in a class that can work with any worksheet, and have a class that's responsible for the model of that runtime-created sheet.

IMO a standard module should only be used to expose entry points (macros, UDFs, Rubberduck test methods, and with Option Private Module, some common utility functions), and contain fairly little code that merely initializes objects and their dependencies, and then it's classes all the way down.

这篇关于在VBA中，应该在类模块中避免修改文档的代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！