Excel实战技巧66：创建向导样式的数据输入窗体3

fanjy

发布于 2019-12-10 12:25:29

6450

发布于 2019-12-10 12:25:29

文章被收录于专栏：完美Excel完美Excel

学习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

好好体会一下上面给出的类的代码，消化一下，继续看下面的内容。

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2019-12-05，如有侵权请联系 cloudcommunity@tencent.com 删除

access

本文分享自完美Excel 微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

access

登录后参与评论

0 条评论

热度

Excel实战技巧66：创建向导样式的数据输入窗体3

Excel实战技巧66：创建向导样式的数据输入窗体3

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐