在 VBA 中使用类有什么好处? [英] What are the benefits of using Classes in VBA?

问题描述

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

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

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.

推荐答案

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

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.

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

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.

我从 我之前的 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.

在 VBA 编辑器中，转到 Insert >类模块.在属性"窗口(默认情况下位于屏幕左下方)中，将模块的名称更改为 WorkLogItem.将以下代码添加到类中:

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:

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

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

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 WorkLogItem that we created above. This name, you would be able to do arr(1).PersonName to get the person's name. That makes your code much easier to read.

让我们继续看这个例子.我们将尝试使用 collection，而不是将对象存储在数组中.

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

接下来，添加一个新的类模块并将其命名为ProcessWorkLog.将以下代码放入其中:

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

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

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

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:

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

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

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.

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

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

为什么这有帮助?

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

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

您需要做的就是向 WorkLogItem 添加一个属性，如下所示:

All you need to do is add a property to WorkLogItem like so:

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

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

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 ProcessWorkLog class:

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

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

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:

    Debug.Print pwl.GetHoursOnBreak("Fred")

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

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.

要进一步阅读，我强烈建议您获取一份VBA 开发人员手册，第二版.这本书充满了很好的例子和最佳实践以及大量的示例代码.如果您正在为一个严肃的项目在 VBA 上投入大量时间，那么花时间阅读本书是非常值得的.

For further reading, I would highly recommend 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 中使用类有什么好处?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！