何时在 VBA 中使用类？

When to use a Class in VBA?

提问by Curtis Inderwiesche

When is it appropriate to use a class in Visual Basic for Applications (VBA)?

什么时候适合在 Visual Basic for Applications (VBA) 中使用类？

I'm assuming the accelerated development and reduction of introducing bugsis a common benefit for most languages that support OOP. But with VBA, is there a specific criterion?

我假设加速开发和减少引入错误是大多数支持 OOP 的语言的共同好处。但是对于 VBA，是否有特定的标准？

回答by Joe

It depends on who's going to develop and maintain the code. Typical "Power User" macro writers hacking small ad-hoc apps may well be confused by using classes. But for serious development, the reasons to use classes are the same as in other languages. You have the same restrictions as VB6 - no inheritance - but you can have polymorphism by using interfaces.

这取决于谁来开发和维护代码。典型的“高级用户”宏编写者可能会因使用类而混淆小型临时应用程序。但是对于认真的开发，使用类的原因与其他语言相同。您具有与 VB6 相同的限制 - 没有继承 - 但是您可以通过使用接口来实现多态性。

A good use of classes is to represent entities, and collections of entities. For example, I often see VBA code that copies an Excel range into a two-dimensional array, then manipulates the two dimensional array with code like:

类的一个很好的用途是表示实体和实体的集合。例如，我经常看到将 Excel 范围复制到二维数组中的 VBA 代码，然后使用如下代码操作二维数组：

Total = 0
For i = 0 To NumRows-1
    Total = Total + (OrderArray(i,1) * OrderArray(i,3))
Next i

It's more readable to copy the range into a collection of objects with appropriately-named properties, something like:

将范围复制到具有适当命名属性的对象集合中更具可读性，例如：

Total = 0
For Each objOrder in colOrders
    Total = Total + objOrder.Quantity * objOrder.Price
Next i

Another example is to use classes to implement the RAII design pattern (google for it). For example, one thing I may need to do is to unprotect a worksheet, do some manipulations, then protect it again. Using a class ensures that the worksheet will always be protected again even if an error occurs in your code:

另一个例子是使用类来实现 RAII 设计模式（google for it）。例如，我可能需要做的一件事是取消保护工作表，进行一些操作，然后再次保护它。使用类可确保即使代码中出现错误，工作表也将始终受到保护：

--- WorksheetProtector class module ---

Private m_objWorksheet As Worksheet
Private m_sPassword As String

Public Sub Unprotect(Worksheet As Worksheet, Password As String)
    ' Nothing to do if we didn't define a password for the worksheet
    If Len(Password) = 0 Then Exit Sub

    ' If the worksheet is already unprotected, nothing to do
    If Not Worksheet.ProtectContents Then Exit Sub

    ' Unprotect the worksheet
    Worksheet.Unprotect Password

    ' Remember the worksheet and password so we can protect again
    Set m_objWorksheet = Worksheet
    m_sPassword = Password
End Sub

Public Sub Protect()
    ' Protects the worksheet with the same password used to unprotect it
    If m_objWorksheet Is Nothing Then Exit Sub
    If Len(m_sPassword) = 0 Then Exit Sub

    ' If the worksheet is already protected, nothing to do
    If m_objWorksheet.ProtectContents Then Exit Sub

    m_objWorksheet.Protect m_sPassword
    Set m_objWorksheet = Nothing
    m_sPassword = ""
End Sub

Private Sub Class_Terminate()
    ' Reprotect the worksheet when this object goes out of scope
    On Error Resume Next
    Protect
End Sub

You can then use this to simplify your code:

然后您可以使用它来简化您的代码：

Public Sub DoSomething()
   Dim objWorksheetProtector as WorksheetProtector
   Set objWorksheetProtector = New WorksheetProtector
   objWorksheetProtector.Unprotect myWorksheet, myPassword

   ... manipulate myWorksheet - may raise an error

End Sub 

When this Sub exits, objWorksheetProtector goes out of scope, and the worksheet is protected again.

当此 Sub 退出时，objWorksheetProtector 超出范围，工作表再次受到保护。

回答by ajp

I think the criteria is the same as other languages

我认为标准与其他语言相同

If you need to tie together several pieces of data and some methods and also specifically handle what happens when the object is created/terminated, classes are ideal

如果您需要将多条数据和一些方法联系在一起，并且还需要专门处理创建/终止对象时发生的情况，则类是理想的

say if you have a few procedures which fire when you open a form and one of them is taking a long time, you might decide you want to time each stage......

假设您有几个程序会在您打开表单时触发，而其中一个程序需要很长时间，您可能会决定要为每个阶段计时......

You could create a stopwatch class with methods for the obvious functions for starting and stopping, you could then add a function to retrieve the time so far and report it in a text file, using an argument representing the name of the process being timed. You could write logic to log only the slowest performances for investigation.

您可以创建一个秒表类，其中包含用于启动和停止的明显函数的方法，然后您可以添加一个函数来检索到目前为止的时间并将其报告在文本文件中，使用表示正在计时的进程的名称的参数。您可以编写逻辑来仅记录最慢的性能以进行调查。

You could then add a progress bar object with methods to open and close it and to display the names of the current action, along with times in ms and probable time remaining based on previous stored reports etc

然后，您可以添加一个带有打开和关闭方法的进度条对象，并显示当前操作的名称，以及基于以前存储的报告等的毫秒时间和可能的剩余时间

Another example might be if you dont like Access's user group rubbish, you can create your own User class with methods for loging in and out and features for group-level user access control/auditing/logging certain actions/tracking errors etc

另一个例子可能是，如果您不喜欢 Access 的用户组垃圾，您可以创建自己的 User 类，其中包含用于登录和注销的方法以及用于组级用户访问控制/审计/记录某些操作/跟踪错误等的功能

Of course you could do this using a set of unrelated methods and lots of variable passing, but to have it all encapsulated in a class just seems better to me.

当然，您可以使用一组不相关的方法和大量的变量传递来做到这一点，但是将它们全部封装在一个类中对我来说似乎更好。

You do sooner or later come near to the limits of VBA, but its quite a powerful language and if your company ties you to it you can actually get some good, complex solutions out of it.

你迟早会接近 VBA 的极限，但它是一种非常强大的语言，如果你的公司将你与它联系起来，你实际上可以从中获得一些好的、复杂的解决方案。

回答by Mike

Classes are extremely useful when dealing with the more complex API functions, and particularly when they require a data structure.

类在处理更复杂的 API 函数时非常有用，尤其是当它们需要数据结构时。

For example, the GetOpenFileName() and GetSaveFileName() functions take an OPENFILENAME stucture with many members. you might not need to take advantage of all of them but they are there and should be initialized.

例如，GetOpenFileName() 和 GetSaveFileName() 函数采用具有许多成员的 OPENFILENAME 结构。您可能不需要利用所有这些，但它们就在那里并且应该被初始化。

I like to wrap the structure (UDT) and the API function declarations into a CfileDialog class. The Class_Initialize event sets up the default values of the structure's members, so that when I use the class, I only need to set the members I want to change (through Property procedures). Flag constants are implemented as an Enum. So, for example, to choose a spreadsheet to open, my code might look like this:

我喜欢将结构 (UDT) 和 API 函数声明包装到 CfileDialog 类中。Class_Initialize 事件设置了结构成员的默认值，这样当我使用该类时，我只需要设置我想要更改的成员（通过 Property 过程）。标志常量被实现为一个枚举。因此，例如，要选择要打开的电子表格，我的代码可能如下所示：

Dim strFileName As String
Dim dlgXLS As New CFileDialog

With dlgXLS
  .Title = "Choose a Spreadsheet"
  .Filter = "Excel (*.xls)|*.xls|All Files (*.*)|*.*"
  .Flags = ofnFileMustExist OR ofnExplorer

  If OpenFileDialog() Then
    strFileName = .FileName
  End If
End With
Set dlgXLS = Nothing

The class sets the default directory to My Documents, though if I wanted to I could change it with the InitDir property.

该类将默认目录设置为 My Documents，但如果我愿意，我可以使用 InitDir 属性更改它。

This is just one example of how a class can be hugely beneficial in a VBA application.

这只是一个类如何在 VBA 应用程序中发挥巨大作用的示例。

回答by cori

I wouldn't say there's a specific criterion, but I've never really found a useful place to use Classes in VBA code. In my mind it's so tied to the existing models around the Office apps that adding additional abstraction outside of that object model just confuses things.

我不会说有一个特定的标准，但我从来没有真正找到在 VBA 代码中使用类的有用地方。在我看来，它与 Office 应用程序周围的现有模型如此紧密地联系在一起，以至于在该对象模型之外添加额外的抽象只会使事情变得混乱。

That's not to say one couldn'tfind a useful place for a class in VBA, or do perfectly useful things using a class, just that I've never found them useful in that environment.

这并不是说一个无法找到一个有用的地方在VBA类，或使用类做的非常有用的东西，只是我从来没有发现他们在这种环境中非常有用。

回答by JonnyGold

You can also reuse VBA code without using actual classes. For example, if you have a called, VBACode. You can access any function or sub in any module with the following syntax:

您还可以在不使用实际类的情况下重用 VBA 代码。例如，如果您有一个被调用的 VBACode。您可以使用以下语法访问任何模块中的任何函数或子：

VBCode.mysub(param1, param2)

If you create a reference to a template/doc (as you would a dll), you can reference code from other projects in the same way.

如果您创建对模板/文档的引用（就像创建 dll 一样），您可以以相同的方式引用其他项目中的代码。

回答by Smandoli

For data recursion (a.k.a. BOM handling), a custom class is critically helpful and I think sometimes indispensable. You can make a recursive function without a class module, but a lot of data issues can't be addressed effectively.

对于数据递归（又名 BOM 处理），自定义类非常有用，我认为有时是必不可少的。你可以在没有类模块的情况下制作递归函数，但是很多数据问题无法有效解决。

(I don't know why people aren't out peddling BOM library-sets for VBA. Maybe the XML tools have made a difference.)

（我不知道为什么人们不为 VBA 兜售 BOM 库集。也许 XML 工具有所作为。）

Multiple form instances is the common application of a class (many automation problems are otherwise unsolvable), I assume the question is about customclasses.

多个表单实例是一个类的常见应用（许多自动化问题否则无法解决），我认为问题与自定义类有关。

回答by Oorang

I use classes when I need to do something and a class will do it best:) For instance if you need to respond to (or intercept) events, then you need a class. Some people hate UDTs (user defined types) but I like them, so I use them if I want plain-english self-documenting code. Pharmacy.NCPDP being a lot easier to read then strPhrmNum :) But a UDT is limited, so say I want to be able to set Pharmacy.NCPDP and have all the other properties populate. And I also want make it so you can't accidentally alter the data. Then I need a class, because you don't have readonly properties in a UDT, etc.

当我需要做某事时，我会使用类，而类会做得最好:) 例如，如果您需要响应（或拦截）事件，那么您需要一个类。有些人讨厌 UDT（用户定义的类型），但我喜欢它们，所以如果我想要纯英文的自文档代码，我会使用它们。Pharmacy.NCPDP 比 strPhrmNum 更容易阅读 :) 但是 UDT 是有限的，所以说我希望能够设置 Pharmacy.NCPDP 并填充所有其他属性。而且我还想制作它，这样您就不会意外更改数据。然后我需要一个类，因为您在 UDT 等中没有只读属性。

Another consideration is just simple readability. If you are doing complex data structures, it's often beneficial to know you just need to call Company.Owner.Phone.AreaCode then trying to keep track of where everything is structured. Especially for people who have to maintain that codebase 2 years after you left:)

另一个考虑是简单的可读性。如果您正在处理复杂的数据结构，知道您只需要调用 Company.Owner.Phone.AreaCode 然后尝试跟踪一切结构的位置通常是有益的。特别是对于那些在你离开 2 年后必须维护该代码库的人:)

My own two cents is "Code With Purpose". Don't use a class without a reason. But if you have a reason then do it:)

我自己的两分钱是“有目的的代码”。不要无故使用类。但如果你有理由，那就去做吧:)

回答by Jason TEPOORTEN

I use classes if I want to create an self-encapsulated package of code that I will use across many VBA projects that come across for various clients.

如果我想创建一个自封装的代码包，我将使用类，我将在为各种客户端遇到的许多 VBA 项目中使用该代码包。

回答by jinsungy

Developing software, even with Microsoft Access, using Object Oriented Programming is generally a good practice. It will allow for scalability in the future by allowing objects to be loosely coupled, along with a number of advantages. This basically means that the objects in your system will be less dependent on each other, so refactoring becomes a lot easier. You can achieve this is Access using Class Modules. The downside is that you cannot perform Class Inheritance or Polymorphism in VBA. In the end, there's no hard and fast rule about using classes, just best practices. But keep in mind that as your application grows, the easier it is to maintain using classes.

使用面向对象编程开发软件，即使是使用 Microsoft Access，通常也是一种很好的做法。它将通过允许对象松散耦合来实现未来的可扩展性，以及许多优点。这基本上意味着您系统中的对象将减少彼此的依赖，因此重构变得更加容易。您可以使用类模块实现访问。缺点是您不能在 VBA 中执行类继承或多态性。最后，关于使用类没有硬性规定，只有最佳实践。但请记住，随着应用程序的增长，使用类进行维护就越容易。

回答by Bellow Akambi

You can define a sql wrapper class in access that is more convenient than the recordsets and querydefs. For example if you want to update a table based on a criteria in another related table, you cannot use joins. You could built a vba recorset and querydef to do that however i find it easier with a class. Also, your application can have some concept that need more that 2 tables, it might be better imo to use classes for that. E.g. You application track incidents. Incident have several attributes that will hold in several tables {users and their contacts or profiles, incident description; status tracking; Checklists to help the support officer to reply tonthe incident; Reply ...} . To keep track of all the queries and relationships involved, oop can be helpful. It is a relief to be able to do Incident.Update(xxx) instead of all the coding ...

您可以在访问中定义一个比记录集和查询定义更方便的 sql 包装类。例如，如果要根据另一个相关表中的条件更新表，则不能使用联接。您可以构建一个 vba recorset 和 querydef 来做到这一点，但是我发现使用类更容易。此外，您的应用程序可能有一些需要超过 2 个表的概念，最好为此使用类。例如，您的应用程序跟踪事件。事件有几个属性，这些属性将保存在几个表中{用户及其联系人或配置文件，事件描述；状态追踪；帮助支持人员回复事件的清单；回复 ...} 。为了跟踪所有涉及的查询和关系，oop 可能会有所帮助。能够做 Incident.Update(xxx) 而不是所有的编码是一种解脱......