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

  • 2019 年 12 月 10 日
  • 筆記

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

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

VirMach 便宜 VPS

QNews

QNews