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