在 VBA 中使用类有什么好处？

What are the benefits of using Classes in VBA?

提问by Margareta

I am doing some VBA programming in Excel and have one workbook where all the datasheets are to be copied from into another sheet. The new sheet will have several header rows, and I would like to keep track of where they are situated so I don't have to find words in them constantly.

我正在 Excel 中进行一些 VBA 编程，并且有一个工作簿，其中所有数据表都将被复制到另一张工作表中。新工作表将有几个标题行，我想跟踪它们的位置，这样我就不必经常在其中查找单词。

Is the simplest thing to use classes and keep them running while the Excel workbook is open? Or will this make it heavy and hard to handle, and I should keep working with subroutines? What are the benefits of using classes? It is not like I have several objects, only sheets and validation on columns.

在 Excel 工作簿打开时使用类并保持它们运行是最简单的方法吗？或者这会使其变得沉重而难以处理，我应该继续使用子程序吗？使用类有什么好处？这不像我有几个对象，只有工作表和列上的验证。

回答by Ben McCormack

The advantage of using classes instead of just subroutines is that classes create a level of abstraction that allow you to write cleaner code. Admittedly, if you've never used classes before in VBA, there is a learning curve, but I believe it's certainly worth the time to figure it out.

使用类而不仅仅是子例程的优点是类创建了一个抽象级别，允许您编写更清晰的代码。诚然，如果您以前从未在 VBA 中使用过类，则有一个学习曲线，但我相信花时间去弄清楚它肯定是值得的。

One key indication that you should switch to classes is if you're constantly adding parameters to your functions and subroutines. In this case, it's almost always best to use classes.

您应该切换到类的一个关键迹象是，您是否不断向函数和子例程添加参数。在这种情况下，几乎总是最好使用类。

I've copied an explanation of classes from one of my previous Stack Overflow answers:

我从我之前的 Stack Overflow 答案之一中复制了类的解释：

---

---

Here's a long example of how using a class might help you. Although this example is lengthy, it will show you how a few principles of object-oriented programming can really help you clean up your code.

这是一个很长的示例，说明使用类可能如何帮助您。虽然这个例子很长，但它会向您展示一些面向对象编程的原则如何真正帮助您清理代码。

In the VBA editor, go to Insert > Class Module. In the Properties window (bottom left of the screen by default), change the name of the module to WorkLogItem. Add the following code to the class:

在 VBA 编辑器中，转到Insert > Class Module. 在“属性”窗口（默认情况下屏幕左下角）中，将模块的名称更改为WorkLogItem。将以下代码添加到类中：

Option Explicit

Private pTaskID As Long
Private pPersonName As String
Private pHoursWorked As Double

Public Property Get TaskID() As Long
    TaskID = pTaskID
End Property

Public Property Let TaskID(lTaskID As Long)
    pTaskID = lTaskID
End Property

Public Property Get PersonName() As String
    PersonName = pPersonName
End Property

Public Property Let PersonName(lPersonName As String)
    pPersonName = lPersonName
End Property

Public Property Get HoursWorked() As Double
    HoursWorked = pHoursWorked
End Property

Public Property Let HoursWorked(lHoursWorked As Double)
    pHoursWorked = lHoursWorked
End Property

The above code will give us a strongly-typed object that's specific to the data with which we're working. When you use multi-dimension arrays to store your data, your code resembles this: arr(1,1)is the ID, arr(1,2)is the PersonName, and arr(1,3)is the HoursWorked. Using that syntax, it's hard to know what is what. Let's assume you still load your objects into an array, but instead use the WorkLogItemthat we created above. This name, you would be able to do arr(1).PersonNameto get the person's name. That makes your code much easier to read.

上面的代码将为我们提供一个强类型对象，该对象特定于我们正在处理的数据。当您使用多维数组来存储您的数据时，您的代码类似于：arr(1,1)是 ID，arr(1,2)是 PersonName，arr(1,3)是 HoursWorked。使用这种语法，很难知道什么是什么。假设您仍然将对象加载到数组中，而是使用WorkLogItem我们上面创建的 。有了这个名字，你就可以做到arr(1).PersonName得到这个人的名字了。这使您的代码更易于阅读。

Let's keep moving with this example. Instead of storing the objects in array, we'll try using a collection.

让我们继续这个例子。我们将尝试使用collection.

Next, add a new class module and call it ProcessWorkLog. Put the following code in there:

接下来，添加一个新的类模块并调用它ProcessWorkLog。将以下代码放入其中：

Option Explicit

Private pWorkLogItems As Collection

Public Property Get WorkLogItems() As Collection
    Set WorkLogItems = pWorkLogItems
End Property

Public Property Set WorkLogItems(lWorkLogItem As Collection)
    Set pWorkLogItems = lWorkLogItem
End Property

Function GetHoursWorked(strPersonName As String) As Double
    On Error GoTo Handle_Errors
    Dim wli As WorkLogItem
    Dim doubleTotal As Double
    doubleTotal = 0
    For Each wli In WorkLogItems
        If strPersonName = wli.PersonName Then
            doubleTotal = doubleTotal + wli.HoursWorked
        End If
    Next wli

Exit_Here:
    GetHoursWorked = doubleTotal
        Exit Function

Handle_Errors:
        'You will probably want to catch the error that will '
        'occur if WorkLogItems has not been set '
        Resume Exit_Here


End Function

The above class is going to be used to "do something" with a colleciton of WorkLogItem. Initially, we just set it up to count the total number of hours worked. Let's test the code we wrote. Create a new Module (not a class module this time; just a "regular" module). Paste the following code in the module:

上面的类将用于通过集合“做某事” WorkLogItem。最初，我们只是将其设置为计算工作总小时数。让我们测试一下我们写的代码。创建一个新模块（这次不是类模块；只是一个“常规”模块）。将以下代码粘贴到模块中：

Option Explicit

Function PopulateArray() As Collection
    Dim clnWlis As Collection
    Dim wli As WorkLogItem
    'Put some data in the collection'
    Set clnWlis = New Collection

    Set wli = New WorkLogItem
    wli.TaskID = 1
    wli.PersonName = "Fred"
    wli.HoursWorked = 4.5
    clnWlis.Add wli

    Set wli = New WorkLogItem
    wli.TaskID = 2
    wli.PersonName = "Sally"
    wli.HoursWorked = 3
    clnWlis.Add wli

    Set wli = New WorkLogItem
    wli.TaskID = 3
    wli.PersonName = "Fred"
    wli.HoursWorked = 2.5
    clnWlis.Add wli

    Set PopulateArray = clnWlis
End Function

Sub TestGetHoursWorked()
    Dim pwl As ProcessWorkLog
    Dim arrWli() As WorkLogItem
    Set pwl = New ProcessWorkLog
    Set pwl.WorkLogItems = PopulateArray()
    Debug.Print pwl.GetHoursWorked("Fred")

End Sub

In the above code, PopulateArray()simply creates a collection of WorkLogItem. In your real code, you might create class to parse your Excel sheets or your data objects to fill a collection or an array.

在上面的代码中，PopulateArray()简单地创建了一个WorkLogItem. 在您的实际代码中，您可能会创建类来解析 Excel 工作表或数据对象以填充集合或数组。

The TestGetHoursWorked()code simply demonstrates how the classes were used. You notice that ProcessWorkLogis instantiated as an object. After it is instantiated, a collection of WorkLogItembecomes part of the pwlobject. You notice this in the line Set pwl.WorkLogItems = PopulateArray(). Next, we simply call the function we wrote which acts upon the collection WorkLogItems.

该TestGetHoursWorked()代码仅演示了如何使用这些类。您注意到它ProcessWorkLog被实例化为一个对象。实例化后，集合WorkLogItem成为pwl对象的一部分。你注意到这一行Set pwl.WorkLogItems = PopulateArray()。接下来，我们只需调用我们编写的作用于集合的函数WorkLogItems。

Why is this helpful?

为什么这有帮助？

Let's suppose your data changes and you want to add a new method. Suppose your WorkLogItemnow includes a field for HoursOnBreakand you want to add a new method to calculate that.

假设您的数据发生变化并且您想要添加一个新方法。假设您WorkLogItem现在包含一个字段，HoursOnBreak并且您想添加一个新方法来计算它。

All you need to do is add a property to WorkLogItemlike so:

您需要做的就是添加一个属性来WorkLogItem像这样：

Private pHoursOnBreak As Double

Public Property Get HoursOnBreak() As Double
    HoursOnBreak = pHoursOnBreak
End Property

Public Property Let HoursOnBreak(lHoursOnBreak As Double)
    pHoursOnBreak = lHoursOnBreak
End Property

Of course, you'll need to change your method for populating your collection (the sample method I used was PopulateArray(), but you probably should have a separate class just for this). Then you just add your new method to your ProcessWorkLogclass:

当然，您需要更改填充集合的方法（我使用的示例方法是PopulateArray()，但您可能应该为此创建一个单独的类）。然后你只需将你的新方法添加到你的ProcessWorkLog类中：

Function GetHoursOnBreak(strPersonName As String) As Double
     'Code to get hours on break
End Function

Now, if we wanted to update our TestGetHoursWorked()method to return result of GetHoursOnBreak, all we would have to do as add the following line:

现在，如果我们想更新我们的TestGetHoursWorked()方法以返回 的结果GetHoursOnBreak，我们只需添加以下行：

    Debug.Print pwl.GetHoursOnBreak("Fred")

If you passed in an array of values that represented your data, you would have to find every place in your code where you used the arrays and then update it accordingly. If you use classes (and their instantiated objects) instead, you can much more easily update your code to work with changes. Also, when you allow the class to be consumed in multiple ways (perhaps one function needs only 4 of the objects properties while another function will need 6), they can still reference the same object. This keeps you from having multiple arrays for different types of functions.

如果传入表示数据的值数组，则必须在代码中找到使用数组的每个位置，然后相应地更新它。如果您改用类（及其实例化的对象），您可以更轻松地更新代码以处理更改。此外，当您允许以多种方式使用类时（也许一个函数只需要 4 个对象属性，而另一个函数需要 6 个），它们仍然可以引用同一个对象。这可以防止您为不同类型的函数使用多个数组。

For further reading, I would highlyrecommend getting a copy of VBA Developer's Handbook, 2nd edition. The book is full of great examples and best practices and tons of sample code. If you're investing a lot of time into VBA for a serious project, it's well worth your time to look into this book.

为了进一步阅读，我强烈建议您获取VBA Developer's Handbook, 2nd edition的副本。这本书充满了很好的例子和最佳实践以及大量的示例代码。如果您正在为一个严肃的项目在 VBA 上投入大量时间，那么花时间阅读本书是非常值得的。

回答by Carl Rippon

If there are lots of subroutines or subroutines are very long then structuring the code in to classes may help. If there are only a couple of subroutines, say, each being only 10 lines of code each then this is over kill. The benefit of structuring the code in to classes is that it is easier to read and change when you come back to it down the line. So another reason to structuring the code into classes is if the code is likely to need changing down the line

如果有很多子例程或子例程很长，那么将代码结构化为类可能会有所帮助。如果只有几个子例程，比如说，每个子例程只有 10 行代码，那么这已经结束了。将代码结构化为类的好处是，当您返回时更容易阅读和更改。因此，将代码构建为类的另一个原因是代码是否可能需要更改

回答by Orphid

There is one other thing you could add to the advantages other contributors have stated (sorry if it's somewhere in Ben McCormack's excellent answer and I missed it). Classes can have their uses if your VBA script is likely to be re-programmed at some point.

除了其他贡献者所说的优势之外，您还可以添加另一件事（对不起，如果它在 Ben McCormack 的出色答案中的某个地方而我错过了）。如果您的 VBA 脚本可能在某个时候被重新编程，则类可以有其用途。

For instance, I am designing a sort of order management system. It is to be used by several colleagues for quite a while, but it may need re-progamming if ordering rules change. I have therefore designed a basic stock item class, which gathers all the information about a stock item. The rules about how this data is analyzed for any order are, however, written in easily accessible and well commented sub routines. By doing this, I hope that future VBA programmers can easily change the mathematical rules by which orders are generated, without having to deal with how all the data is gathered about a particular stock item (this is all done by subroutines and functions within the class, which are activated when the class is handed a stock number). A Class' public properties are also picked up by intellisense, allowing the next programmer, as well as yourself, to have an easier time of it.

例如，我正在设计一种订单管理系统。会被几个同事使用一段时间，但如果排序规则发生变化，可能需要重新编程。因此，我设计了一个基本的库存项目类，它收集有关库存项目的所有信息。然而，关于如何针对任何订单分析这些数据的规则是用易于访问且注释良好的子程序编写的。通过这样做，我希望未来的 VBA 程序员可以轻松地更改生成订单的数学规则，而无需处理如何收集有关特定库存项目的所有数据（这一切都由类中的子例程和函数完成） ，当班级收到股票编号时激活）。类的公共属性也被智能感知拾取，允许下一个程序员，

I guess the point is that classes can make life easier for later users in this way ifthey encode some basic set of information, or some conceptual object, that is always likely to be relevant to the context of the program's use.

我猜的一点是，类可以使生活更方便以后用户通过这种方式，如果它们编码的一些基本信息集，或一些概念性的对象，那就是总是可能相关的程序使用的上下文。