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

问题描述

我在Excel 2007中做了一些VBA编程，并有一个工作簿，所有的数据表都要复制到另一个工作表。新工作表将有几个标题行，我想跟踪他们的位置，所以我不必在他们不断找到的话。

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

谢谢！

解决方案

使用类而不是子程序的优点是类创建了一个抽象级别，允许你编写更干净的代码。当然，如果你从来没有在VBA中使用过类，那么就有一个学习曲线，但是我相信这是值得花时间去研究的。

你应该切换到类是如果你不断地添加参数到你的函数和子程序。在这种情况下，几乎总是最好使用类。

我复制了来自我之前的Stack Overflow答案之一：

---

这里有一个很好的例子，说明如何使用类可以帮助你。虽然这个例子很冗长，但它会告诉你面向对象编程的几个原则真正能帮助你清理你的代码。

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

 选项显式
 
私有pTaskID As Long 
 private pPersonName As String 
私人pHours工作为双人
 
公共财产获取TaskID（）As Long 
 TaskID = pTaskID 
结束属性
 
属性让TaskID（lTask​​ID As Long）
 pTaskID = lTask​​ID 
结束属性
 
公共属性Get PersonName（）As String 
 PersonName = pPersonName 
 
 
公共属性让PersonName（lPersonName As String）
 pPersonName = lPersonName 
结束属性
 
公共属性Get HoursWorked（）As Double 
 HoursWorked = pHoursWorked 
结束资产
 
公共财产让HoursWorkeded（1Hours工作双）
 pHoursWorked = 1HoursWorked 
结束资产
  

上面的代码将给我们一个强类型的对象，它是我们正在使用的数据所特有的。当您使用多维数组存储数据时，您的代码类似于： arr（1,1）是ID， arr 2）是PersonName， arr（1,3）是HoursWorked。使用该语法，很难知道是什么。让我们假设你仍然把你的对象加载到数组，而是使用我们在上面创建的 WorkLogItem 。这个名字，你可以通过 arr（1）.PersonName 来获得这个人的名字。这让你的代码更容易阅读。

让我们继续移动这个例子。而不是在数组中存储对象，我们将尝试使用集合。

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

 选项显式
 
私有pWorkLogItems作为集合
 
 Public Property Get WorkLogItems（）As Collection 
 Set WorkLogItems = pWorkLogItems 
结束属性
 
公共属性Set WorkLogItems（lWorkLogItem As Collection）
设置pWorkLogItems = lWorkLogItem 
结束属性
 
函数GetHoursWorked（strPersonName As String）As Double 
 On Error GoTo Handle_Errors 
 Dim wli As WorkLogItem 
 Dim doubleTotal As Double 
 doubleTotal = 0 
对于WorkLogItems中的每个wli如果strPersonName = wli.PersonName然后
 doubleTotal = doubleTotal + wli.HoursWorked 
结束如果
下一个wli 
 
 Exit_Here：
 GetHoursWorked = doubleTotal 
退出函数
 
 Handle_Errors：
'你可能想要捕获错误'
 '如果WorkLogItems尚未设置，则发生'
 Resume Exit_Here 
 
 
结束函数
  

上面的类将被用来做某事与 WorkLogItem 的集合。最初，我们只是设置它计算工作的总小时数。让我们测试我们写的代码。创建一个新的模块（这次不是类模块;只是一个常规模块）。在模块中粘贴以下代码：

 选项显式
 
函数PopulateArray $ b Dim clnWlis As Collection 
 Dim wli As WorkLogItem 
'将一些数据添加到集合'
中set clnWlis =新集合
 
设置wli = New WorkLogItem 
 wli.TaskID = 1 
 wli.PersonName =Fred
 wli.HoursWorked = 4.5 
 clnWlis.Add wli 
 
设置wli = New WorkLogItem 
 wli.TaskID = 2 
 wli.PersonName =Sally
 wli.HoursWorked = 3 
 clnWlis.Add wli 
 
设置wli = New WorkLogItem 
 wli.TaskID = 3 
 wli.PersonName =Fred
 wli.HoursWorked = 2.5 
 clnWlis.Add wli 
 
设置PopulateArray = clnWlis 
 End Function 
 
 Sub TestGetHoursWorked（）
 Dim pwl As ProcessWorkLog 
 Dim arrWli（）As WorkLogItem 
设置pwl =新的ProcessWorkLog 
设置pwl.WorkLogItems = PopulateArray（）
 Debug.Print pwl.GetHoursWorked（Fred）
 
 End Sub 
  

$ b b

在上面的代码中， PopulateArray（）只是创建了一个 WorkLogItem 的集合。在实际代码中，您可以创建类来解析Excel表或数据对象，以填充集合或数组。

TestGetHoursWorked ）代码只是演示如何使用类。你注意到 ProcessWorkLog 被实例化为一个对象。实例化之后， WorkLogItem 的集合将成为 pwl 对象的一部分。你注意到这行 Set pwl.WorkLogItems = PopulateArray（）。接下来，我们简单地调用我们编写的函数，作用于 WorkLogItems 。

/ p>

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

所有你需要做的是添加一个属性到 WorkLogItem 如下：

 私人pHoursOnBreak作为双人
 
公共财产获取HoursOnBreak（）作为双人
 HoursOnBreak = pHoursOnBreak 
结束Property 
 
公共财产Let HoursOnBreak（1HoursOnBreak As Double）
 pHoursOnBreak = 1HoursOnBreak 
结束资产
  

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

  GetHoursOnBreak（strPersonName As String）As Double 
'获取休息小时的代码
结束函数
  
 
 $ b b 
现在，如果我们要更新 TestGetHoursWorked（）方法返回 GetHoursOnBreak 的结果，必须添加以下行：
  Debug.Print pwl.GetHoursOnBreak（Fred）
  
如果你传递一个代表你的数据的值数组，你必须找到你使用的代码中的每一个地方然后相应地更新它。如果你使用类（和它们的实例化对象），你可以更容易地更新代码来处理更改。另外，当允许以多种方式使用类时（或许一个函数只需要4个对象属性，而另一个函数需要6个），它们仍然可以引用同一个对象。 
 
 
 
要进一步阅读，我会高度建议您取得 VBA开发人员手册，第2版。这本书充满了伟大的例子和最佳实践以及大量的示例代码。如果你在一个严肃的项目投入了大量的时间在VBA，你值得花时间看看这本书。 
 
I am doing some VBA programming in excel 2007 and have one workbook where all the datasheets is 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. 

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

Thanks!
解决方案
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.

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



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

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

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

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.

Why is this helpful?

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.

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
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
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")
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.

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屋！