我正在Excel中进行一些VBA编程,有一个工作簿,需要将其中所有数据表复制到另一个表中。新表将有几个标题行,我想跟踪它们的位置,以便不必经常查找它们中的单词。
最简单的方法是使用类,并在Excel工作簿打开时让它们保持运行状态吗?还是这会使它变得沉重和难以处理,我应该继续使用子程序?使用类的好处是什么?我并不像拥有几个对象,只有工作表和列上的验证。
以下是一个漫长的示例,展示了如何使用类可以帮助您。虽然这个示例很长,但它将向您展示一些面向对象编程的原则如何真正帮助您整理代码。
在VBA编辑器中,转到插入>类模块
。在属性窗口(默认情况下位于屏幕左下角),将模块的名称更改为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
arr(1,1)
是ID,arr(1,2)
是PersonName,arr(1,3)
是HoursWorked。使用该语法,很难知道哪个是哪个。假设您仍然将对象加载到数组中,但改用我们上面创建的WorkLogItem
。这个名称,您将能够执行arr(1).PersonName
以获取人员姓名。这使得您的代码更容易阅读。collection
而不是在数组中存储对象。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
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
PopulateArray()
仅仅创建了一个WorkLogItem
的集合。在你的实际代码中,你可能会创建一个类来解析你的Excel表格或者数据对象来填充一个集合或者数组。
TestGetHoursWorked()
代码仅仅演示了这些类是如何被使用的。你可以注意到ProcessWorkLog
被实例化为一个对象。在它被实例化之后,一组WorkLogItem
成为了pwl
对象的一部分。你可以在Set pwl.WorkLogItems = PopulateArray()
这一行中注意到这一点。接下来,我们只需要调用我们编写的函数,该函数将作用于WorkLogItems
集合。WorkLogItem
现在包括一个HoursOnBreak
字段,并且你想添加一个新的方法来计算它。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
PopulateArray()
,但您可能应该有一个单独的类来处理此操作)。然后,您只需将新方法添加到ProcessWorkLog
类中即可:Function GetHoursOnBreak(strPersonName As String) As Double
'Code to get hours on break
End Function
TestGetHoursWorked()
方法以返回GetHoursOnBreak
的结果,我们只需添加以下行: Debug.Print pwl.GetHoursOnBreak("Fred")