学习Excel技术,关注微信公众号:
excelperfect
在开始阅读本文前,请先学习下列内容:
Excel实战技巧66:创建向导样式的数据输入窗体1
Excel实战技巧66:创建向导样式的数据输入窗体2
上文中,我们完成了用户窗体界面的设计。接下来,设置一些类来使用户窗体工作。在一开始,你可能会认为一个与数据记录相联系的类就满足要求了,但我们将在定义类时分解功能,设计一两个类帮助定义向导步骤。最终,将实现一个灵活的向导应用程序,提供非常容易修改步骤的顺序的能力,甚至添加一个步骤也相当简单。
HRWizard中的类
由于正收集的某些员工信息将被传递给其它部门去处理,因此在自已的类中放置从每个屏幕中获得的数据。还需要一个监控向导步骤的类,同时考虑一个帮助使用ListMgr工作表中数据填充列表的类。下表列出了每个类及其功能。
表:HRWizard应用程序类模块
图19
HRWizard商业对象
下面开始设计商业对象。这些类存储每个对象的数据,包含每个对象的一些商业规则。
在工程中添加一个新的类模块并将其命名为cPerson,再添加另外三个类模块,分别将它们命名为cAddress、cEquipment和cAccess。cPerson对象包含一个cAddress对象、cEquipment对象、一个cAccess对象。要保持它们同步,对这四个商业对象类的每一个都添加一个ID属性。
在每个类中,添加下列模块级的声明:
Private m_lngID As Long
Public Property Get ID() AsLong
ID = m_lngID
End Property
Public Property Let ID(newID AsLong)
m_lngID = newID
End Property
现在,让我们集中开发cPerson类。每个类实质上对应着先前我们设计的一个界面。
在cPerson类中添加下列模块级变量声明:
Private m_sFName As String
Private m_sMidInit As String
Private m_sLName As String
Private m_dtDOB As Date
Private m_sSSN As String
Private m_sJobTitle As String
Private m_sDepartment As String
Private m_sEmail As String
Private m_oAddress As cAddress
Private m_oEquipment As cEquipment
Private m_oAccess As cAccess
注意,除了从界面设计中的数据输入项外,还包括包含地址、设备和访问信息的对象。
这里首先要做的是初始化cPerson类,设置一些默认值。在Class_Initialize事件中,添加下列代码:
Private Sub Class_Initialize()
m_lngID = RandomNumber(100000, 999999)
Set m_oAddress = New cAddress
Set m_oEquipment = New cEquipment
Set m_oAccess = New cAccess
SetObjectIDs
End Sub
上述代码中,设置了私有的ID变量m_lngID为随机的6位数字,并初始化私有的商业对象变量。然后调用私有的函数SetObjectIDs设置所有四个商业对象的ID值为相同的值。添加下列代码到cPerson类中生成随机数字和同步ID字段:
Private Function RandomNumber(upper As Long, lower As Long) As Long
'生成一个介于upper和lower之间的随机数
Randomize
RandomNumber = Int((upper - lower + 1) *Rnd + lower)
End Function
Private Sub SetObjectIDs()
m_oAddress.ID = m_lngID
m_oEquipment.ID = m_lngID
m_oAccess.ID = m_lngID
End Sub
在ID属性的Property Let函数中添加对上面过程的调用。这样,如果手工对ID字段赋值,那么所有的商业对象都获取这个新值。最终ID属性的Property Let过程代码如下:
Public Property Let ID(newID As Long)
m_lngID = newID
'保持所有对象同步使用相同的ID
SetObjectIDs
End Property
cPerson类代码的剩余部分非常直观。最终的cPerson类的代码如下:
Property Get FName() As String
FName = m_sFName
End Property
Property Let FName(newFName As String)
m_sFName = newFName
End Property
Property Get MidInit() As String
MidInit = m_sMidInit
End Property
Property Let MidInit(newMidInit As String)
m_sMidInit = newMidInit
End Property
Property Get LName() As String
LName = m_sLName
End Property
Property Let LName(newLName As String)
m_sLName = newLName
End Property
Property Get DOB() As Date
DOB = m_dtDOB
End Property
Property Let DOB(newDOB As Date)
m_dtDOB = newDOB
End Property
Property Get SSN() As String
SSN = m_sSSN
End Property
Property Let SSN(newSSN As String)
m_sSSN = newSSN
End Property
Property Get JobTitle() As String
JobTitle = m_sJobTitle
End Property
Property LetJobTitle(newJobTitle As String)
m_sJobTitle = newJobTitle
End Property
Property Get Department() As String
Department = m_sDepartment
End Property
Property LetDepartment(newDepartment As String)
m_sDepartment = newDepartment
End Property
Property Get Email() As String
Email = m_sEmail
End Property
Property Let Email(newEmail As String)
m_sEmail = newEmail
End Property
Property Get Address() As cAddress
Set Address = m_oAddress
End Property
Property Set Address(newAddress As cAddress)
Set m_oAddress = newAddress
End Property
Property Get Equipment() As cEquipment
Set Equipment = m_oEquipment
End Property
Property Set Equipment(newEquipment As cEquipment)
Set m_oEquipment = newEquipment
End Property
Property Get Access() As cAccess
Set Access = m_oAccess
End Property
Property Set Access(newAccess As cAccess)
Set m_oAccess = newAccess
End Property
至此,已经完成Person数据元素的添加,以及3个对象类属性。同时,想要添加一个属性,返回员工的全名。下面的代码在cPerson中添加只读的FullName属性,返回员工全名:
Property Get FullName() As String
Dim sReturn As String
Dim blnMidInit As Boolean
blnMidInit = Len(m_sMidInit &"") > 0
If blnMidInit Then
sReturn = m_sFName & " "& m_sMidInit & " " & m_sLName
Else
sReturn = m_sFName & " "& m_sLName
End If
FullName = sReturn
End Property
这就是我们所需要的cPerson类。
下面列出其它3个类的代码。
cAddress类:
Private m_lngID As Long
Private m_sStreetAddress As String
Private m_sStreetAddress2 As String
Private m_sCity As String
Private m_sState As String
Private m_sZipCode As String
Private m_sPhoneNumber As String
Private m_sCellPhone As String
Public Property Get ID() As Long
ID = m_lngID
End Property
Public Property Let ID(newID As Long)
m_lngID = newID
End Property
Public Property Get StreetAddress() As String
StreetAddress = m_sStreetAddress
End Property
Public Property Let StreetAddress(newAddress As String)
m_sStreetAddress = newAddress
End Property
Public Property Get StreetAddress2() As String
StreetAddress2 = m_sStreetAddress2
End Property
Public Property Let StreetAddress2(newAddress2 As String)
m_sStreetAddress2 = newAddress2
End Property
Public Property Get City() AsString
City = m_sCity
End Property
Public Property Let City(newCity As String)
m_sCity = newCity
End Property
Public Property Get State() As String
State = m_sState
End Property
Public Property Let State(newState As String)
m_sState = newState
End Property
Public Property Get ZipCode() As String
ZipCode = m_sZipCode
End Property
Public Property Let ZipCode(newZipCode As String)
m_sZipCode = newZipCode
End Property
Public Property Get PhoneNumber() As String
PhoneNumber = m_sPhoneNumber
End Property
Public Property Let PhoneNumber(newPhoneNumber As String)
m_sPhoneNumber = newPhoneNumber
End Property
Public Property Get CellPhone() As String
CellPhone = m_sCellPhone
End Property
Public Property Let CellPhone(newCellPhone As String)
m_sCellPhone = newCellPhone
End Property
cEquipment类:
Private m_lngID As Long
Private m_sPCType As String
Private m_sPhoneType As String
Private m_sLocation As String
Private m_sFaxYN As String
Public Property Get ID() AsLong
ID = m_lngID
End Property
Public Property Let ID(newID AsLong)
m_lngID = newID
End Property
Public Property Get PCType() AsString
PCType = m_sPCType
End Property
Public Property Let PCType(newPCType As String)
m_sPCType = newPCType
End Property
Public Property Get PhoneType() As String
PhoneType = m_sPhoneType
End Property
Public Property Let PhoneType(newPhoneType As String)
m_sPhoneType = newPhoneType
End Property
Public Property Get Location() As String
Location = m_sLocation
End Property
Public Property Let Location(newLocation As String)
m_sLocation = newLocation
End Property
Public Property Get FaxYN() As String
FaxYN = m_sFaxYN
End Property
Public Property Let FaxYN(newFaxYN As String)
m_sFaxYN = newFaxYN
End Property
cAccess类:
Private m_lngID As Long
Private m_sBuilding As String
Private m_iNetworkLevel As Integer
Private m_sRemoteYN As String
Private m_sParkingSpot As String
Public Property Get ID() As Long
ID = m_lngID
End Property
Public Property Let ID(newID As Long)
m_lngID = newID
End Property
Public Property Get Building() As String
Building = m_sBuilding
End Property
Public Property Let Building(newBuilding As String)
m_sBuilding = newBuilding
End Property
Public Property Get NetworkLevel() As Integer
NetworkLevel = m_iNetworkLevel
End Property
Public Property Let NetworkLevel(newNetworkLevel As Integer)
m_iNetworkLevel = newNetworkLevel
End Property
Public Property Get RemoteYN() As String
RemoteYN = m_sRemoteYN
End Property
Public Property Let RemoteYN(newRemoteYN As String)
m_sRemoteYN = newRemoteYN
End Property
Public Property Get ParkingSpot() As String
ParkingSpot = m_sParkingSpot
End Property
Public Property Let ParkingSpot(newParkingSpot As String)
m_sParkingSpot = newParkingSpot
End Property
好好体会一下上面给出的类的代码,消化一下,继续看下面的内容。