创建向导样式的数据输入窗体.doc

1、创建向导样式的数据输入窗体2011 年 04 月 12 日, 2:14 下午 引言：本文来源于Pro Excel VBA的第 4 章的示例，主要为学习 VBA 用户窗体提供参考。本文“超长”，但如果能够仔细研读，一定会有很丰富的收获。可以先下载示例文档：向导样式的输入是相当普遍的技术，用于帮助用户输入较多的或复杂的数据。向导允许将数据分成相互联系的部分，通过按顺序输入数据的过程指导用户。示例工作簿名称为 HRWizard.xlsm，由 2 个工作表组成，名为 EmpData 的工作表为员工数据库工作表，名为 ListMgr 的工作表包含在创建数据输入窗体向导时使用的不同的列表。（其实还有一个名

2、为 UFormConfig 的工作表，存放着向导步骤的信息）EmpData 工作表被分成 4 部分：Personal、Address、Equipment、Access，如下列图所示。员工个人信息员工地址信息员工设备信息员工访问信息下面的向导窗体将引导用户为新员工输入信息。布局向导窗体1、打开 VBE，添加新用户窗体。2、将用户窗体的高度设置为 320，宽度为 332。3、将用户窗体重命名为 HRWizard。4、在用户窗体顶部添加标签，将其 Caption 属性设置为：MyCompany HRWizard，设置字体为大尺寸，如 18pt。5、在用户窗体中添加一个多页控件。6、设置该控件的 He

3、ight 属性为 216，Width 属性为 270。7、将其在用户窗体中居中，在用户窗体底部留出空间。此时的用户窗体如下图所示。由于有 4 个数据集合部分，需要再添加两个页。1、在多页控件顶部的选项卡中单击右键。2、在快捷菜单中选择“新建页”，如下图所示。3、重复上面的步骤。此时的用户窗体如下图所示。在用户窗体中添加控件在“Page1”中添加的控件如下表所示，与 EmpData 工作表中个人信息列标题一致。表：HRWizard 用户窗体控件在多页控件的下方添加四个命令按钮。现在的用户窗体和下图相似。在“Page2”中添加的控件如下表所示，与 EmpData 工作表中地址信息列标题一致。表：A

4、ddress 选项卡控件设置Page2 如下图所示。在“Page3”中添加的控件如下表所示，与 EmpData 工作表中设备信息列标题一致。表：设备选项卡控件设置Page3 如下图所示。在“Page4”中添加的控件如下表所示，与 EmpData 工作表中访问信息列标题一致。表：访问选项卡控件设置Page4 如下图所示。至此，界面设计完成。接下来，设置一些类来使用户窗体工作。一开始，可能认为一个与数据记录相联系的类就满足要求了，但我们将在定义类时分解功能区，设计一两个类帮助定义向导步骤。最终，将有一个灵活的向导应用程序，提供非常容易修改步骤的顺序的能力，甚至添加一个步骤也相当简单。HRWizar

5、d 类由于正收集的某些员工信息将被传递给其它部门去处理，因此在自已的类中放置从每个屏幕中获得的数据。也需要一个监控向导步骤的类，同时考虑一个帮助使用 ListMgr 工作表中数据填充列表的类。下表列出了每个类并描述了其功能。表：HRWizard 应用程序类模块类 描述cPerson 包含新记录中的所有个人信息cAddress 包含新记录中的所有地址信息cEquipment 包含新记录中的所有设备信息cAccess 包含新记录中的所有访问信息cStep 包含向导每一步的配置值cStepMgr 控制向导的操作及管理 cStep 对象的集合cListMgr 控制用户窗体中填充组合框的列表cHRDat

6、a从商业对象中将数据转移到数据库；将数据从数据库发送到商业对象HRWizard 商业对象下面开始设计商业对象。这些类存储每个对象的数据，包含每个对象的一些商业规则。在工程中添加一个新的类模块并将其命名为 cPerson，再添加另外三个类模块，分别将它们命名为 cAddress、cEquipment 和 cAccess。cPerson 对象包含一个cAddress 对象、cEquipment 对象、一个 cAccess 对象。要保持它们同步，对这四个商业对象类的每一个都添加一个 ID 属性。在每个类中，添加下列模块级的声明：Private m_lngID As LongPublic Proper

7、ty Get ID() As LongID = m_lngIDEnd PropertyPublic Property Let ID(newID As Long)m_lngID = newIDEnd Property现在，让我们集中开发 cPerson 类。每个类实质上对应着先前我们设计的每一个界面。在 cPerson 类中添加下列模块级变量声明：Private m_sFName As StringPrivate m_sMidInit As StringPrivate m_sLName As StringPrivate m_dtDOB As DatePrivate m_sSSN As Strin

8、gPrivate m_sJobTitle As StringPrivate m_sDepartment As StringPrivate m_sEmail As StringPrivate m_oAddress As cAddressPrivate m_oEquipment As cEquipmentPrivate m_oAccess As cAccess注意，除了从屏幕设计中的数据输入项外，还包括包含地址、设备和访问信息的对象。这里首先要做的是初始化 cPerson 类，设置一些默认值。在 Class_Initialize事件中，添加下列代码：Private Sub Class_Initia

9、lize()m_lngID = RandomNumber(100000, 999999)Set m_oAddress = New cAddressSet m_oEquipment = New cEquipmentSet m_oAccess = New cAccessSetObjectIDsEnd Sub上述代码中，设置了私有的 ID 变量 m_lngID 为随机的 6 位数字，并初始化私有的商业对象变量。然后调用私有的函数 SetObjectIDs 设置所有四个商业对象的ID 值为相同的值。添加下列代码到 cPerson 类中生成随机数字和同步 ID 字段：Private Function R

10、andomNumber(upper As Long, lower As Long) As Long生成一个介于 upper 和 lower 之间的随机数RandomizeRandomNumber = Int(upper - lower + 1) * Rnd + lower)End FunctionPrivate Sub SetObjectIDs()m_oAddress.ID = m_lngIDm_oEquipment.ID = m_lngIDm_oAccess.ID = m_lngIDEnd Sub在 ID Property Let 函数中添加对上面的过程的调用。这样，如果手工对 ID 字段赋

11、值，那么所有的商业对象都获取这个新值。最终的 ID Property Let 过程代码如下：Public Property Let ID(newID As Long)m_lngID = newIDSetObjectIDs 保持所有对象同步使用相同的 IDEnd PropertycPerson 类的剩余部分非常直观。最终的 cPerson 类的代码如下：Property Get FName() As StringFName = m_sFNameEnd PropertyProperty Let FName(newFName As String)m_sFName = newFNameEnd Prop

12、ertyProperty Get MidInit() As StringMidInit = m_sMidInitEnd PropertyProperty Let MidInit(newMidInit As String)m_sMidInit = newMidInitEnd PropertyProperty Get LName() As StringLName = m_sLNameEnd PropertyProperty Let LName(newLName As String)m_sLName = newLNameEnd PropertyProperty Get DOB() As DateDO

13、B = m_dtDOBEnd PropertyProperty Let DOB(newDOB As Date)m_dtDOB = newDOBEnd PropertyProperty Get SSN() As StringSSN = m_sSSNEnd PropertyProperty Let SSN(newSSN As String)m_sSSN = newSSNEnd PropertyProperty Get JobTitle() As StringJobTitle = m_sJobTitleEnd PropertyProperty Let JobTitle(newJobTitle As

14、String)m_sJobTitle = newJobTitleEnd PropertyProperty Get Department() As StringDepartment = m_sDepartmentEnd PropertyProperty Let Department(newDepartment As String)m_sDepartment = newDepartmentEnd PropertyProperty Get Email() As StringEmail = m_sEmailEnd PropertyProperty Let Email(newEmail As Strin

15、g)m_sEmail = newEmailEnd PropertyProperty Get Address() As cAddressSet Address = m_oAddressEnd PropertyProperty Set Address(newAddress As cAddress)Set m_oAddress = newAddressEnd PropertyProperty Get Equipment() As cEquipmentSet Equipment = m_oEquipmentEnd PropertyProperty Set Equipment(newEquipment

16、As cEquipment)Set m_oEquipment = newEquipmentEnd PropertyProperty Get Access() As cAccessSet Access = m_oAccessEnd PropertyProperty Set Access(newAccess As cAccess)Set m_oAccess = newAccessEnd Property至此，已经完成 Person 数据元素的添加，以及 3 个对象类属性。同时，想要添加一个属性，返回员工的全名。下面的代码在 cPerson 中添加只读的 FullName属性：Property Ge

17、t FullName() As StringDim sReturn As StringDim blnMidInit As BooleanblnMidInit = Len(m_sMidInit & “) 0If blnMidInit ThensReturn = m_sFName & “ “ & m_sMidInit & “ “ & m_sLNameElsesReturn = m_sFName & “ “ & m_sLNameEnd IfFullName = sReturnEnd Property这就是我们所需要的 cPerson 类。下面列出其它 3 个类的代码。cAddress 类：Priva

18、te m_lngID As LongPrivate m_sStreetAddress As StringPrivate m_sStreetAddress2 As StringPrivate m_sCity As StringPrivate m_sState As StringPrivate m_sZipCode As StringPrivate m_sPhoneNumber As StringPrivate m_sCellPhone As StringPublic Property Get ID() As LongID = m_lngIDEnd PropertyPublic Property

19、Let ID(newID As Long)m_lngID = newIDEnd PropertyPublic Property Get StreetAddress() As StringStreetAddress = m_sStreetAddressEnd PropertyPublic Property Let StreetAddress(newAddress As String)m_sStreetAddress = newAddressEnd PropertyPublic Property Get StreetAddress2() As StringStreetAddress2 = m_sS

20、treetAddress2End PropertyPublic Property Let StreetAddress2(newAddress2 As String)m_sStreetAddress2 = newAddress2End PropertyPublic Property Get City() As StringCity = m_sCityEnd PropertyPublic Property Let City(newCity As String)m_sCity = newCityEnd PropertyPublic Property Get State() As StringStat

21、e = m_sStateEnd PropertyPublic Property Let State(newState As String)m_sState = newStateEnd PropertyPublic Property Get ZipCode() As StringZipCode = m_sZipCodeEnd PropertyPublic Property Let ZipCode(newZipCode As String)m_sZipCode = newZipCodeEnd PropertyPublic Property Get PhoneNumber() As StringPh

22、oneNumber = m_sPhoneNumberEnd PropertyPublic Property Let PhoneNumber(newPhoneNumber As String)m_sPhoneNumber = newPhoneNumberEnd PropertyPublic Property Get CellPhone() As StringCellPhone = m_sCellPhoneEnd PropertyPublic Property Let CellPhone(newCellPhone As String)m_sCellPhone = newCellPhoneEnd P

23、ropertycEquipment 类：Private m_lngID As LongPrivate m_sPCType As StringPrivate m_sPhoneType As StringPrivate m_sLocation As StringPrivate m_sFaxYN As StringPublic Property Get ID() As LongID = m_lngIDEnd PropertyPublic Property Let ID(newID As Long)m_lngID = newIDEnd PropertyPublic Property Get PCTyp

24、e() As StringPCType = m_sPCTypeEnd PropertyPublic Property Let PCType(newPCType As String)m_sPCType = newPCTypeEnd PropertyPublic Property Get PhoneType() As StringPhoneType = m_sPhoneTypeEnd PropertyPublic Property Let PhoneType(newPhoneType As String)m_sPhoneType = newPhoneTypeEnd PropertyPublic P

25、roperty Get Location() As StringLocation = m_sLocationEnd PropertyPublic Property Let Location(newLocation As String)m_sLocation = newLocationEnd PropertyPublic Property Get FaxYN() As StringFaxYN = m_sFaxYNEnd PropertyPublic Property Let FaxYN(newFaxYN As String)m_sFaxYN = newFaxYNEnd PropertycAcce

26、ss 类：Private m_lngID As LongPrivate m_sBuilding As StringPrivate m_iNetworkLevel As IntegerPrivate m_sRemoteYN As StringPrivate m_sParkingSpot As StringPublic Property Get ID() As LongID = m_lngIDEnd PropertyPublic Property Let ID(newID As Long)m_lngID = newIDEnd PropertyPublic Property Get Building

27、() As StringBuilding = m_sBuildingEnd PropertyPublic Property Let Building(newBuilding As String)m_sBuilding = newBuildingEnd PropertyPublic Property Get NetworkLevel() As IntegerNetworkLevel = m_iNetworkLevelEnd PropertyPublic Property Let NetworkLevel(newNetworkLevel As Integer)m_iNetworkLevel = n

28、ewNetworkLevelEnd PropertyPublic Property Get RemoteYN() As StringRemoteYN = m_sRemoteYNEnd PropertyPublic Property Let RemoteYN(newRemoteYN As String)m_sRemoteYN = newRemoteYNEnd PropertyPublic Property Get ParkingSpot() As StringParkingSpot = m_sParkingSpotEnd PropertyPublic Property Let ParkingSp

29、ot(newParkingSpot As String)m_sParkingSpot = newParkingSpotEnd Property管理列表在 HRWizard 用户窗体中输入的一些数据是通过组合框控件显示给用户的。HRWizard 工作簿文件包含一个名为 ListMgr 的工作表，其中包含每个列表的数据。这些数据存储在 ListMgr 工作表的命名区域。cListManager 类包含的函数可以从这些命名区域中填充组合框，同时也有一个将列表绑定到 VBA Collection 对象的方法。插入一个新的类模块，将其命名为 cListManager，在其中添加下面两个方法：Public

30、 Sub BindListToRange(ListRangeName As String, TheCombo As MSForms.ComboBox)TheCombo.RowSource = ListRangeNameEnd SubPublic Sub BindListToCollection(TheCollection As Collection, TheCombo As MSForms.ComboBox)Dim iNumItems As IntegerDim i As IntegeriNumItems = TheCollection.CountFor i = 1 To iNumItemsT

31、heCombo.AddItem TheCollection(i)Next iEnd SubBindListToRange 方法接受区域名称字符串值和 ComboBox 对象，设置组合框的RowSource 属性为命名区域。BindListToCollection 方法简单地遍历集合并调用组合框的 AddItem 方法。数据类数据类被命名为 cHRData，这是一个专门为 HRWizard 应用程序设计的类。插入一个新的类模块，将其命名为 cHRData。在其中添加下面的模块级变量、一个属性和一个方法。Private m_oWorksheet As WorksheetPrivate m_lngN

32、ewRowNum As LongPrivate m_oEmployee As cPersonPrivate m_oXL As cExcelUtilsPublic Property Get Worksheet() As WorksheetSet Worksheet = m_oWorksheetEnd PropertyPublic Property Set Worksheet(newWorksheet As Worksheet)Set m_oWorksheet = newWorksheetEnd PropertyPublic Function SaveEmployee(Employee As cP

33、erson) As BooleanDim blnReturn As BooleanIf m_oWorksheet Is Nothing ThenGoTo Exit_FunctionEnd Ifm_lngNewRowNum = m_oXL.FindEmptyRow(m_oWorksheet)Set m_oEmployee = EmployeeSaveEmpDataSaveAddressDataSaveEquipmentDataSaveAccessDataExit_Function:SaveEmployee = blnReturnExit FunctionEnd Function添加下列类初始化和

34、清理代码：Private Sub Class_Initialize()Set m_oXL = New cExcelUtilsEnd SubPrivate Sub Class_Terminate()Set m_oXL = NothingEnd SubWorksheet 属性让我们定义工作簿中存储数据的地方。当传递 cPerson 对象时SaveEmployee 方法为我们做一些事情：Public Function SaveEmployee(Employee As cPerson) As Boolean检查是否设置了 Worksheet 属性，以便知道在哪里保存数据：If m_oWorksheet

35、 Is Nothing ThenGoTo Exit_FunctionEnd If使用 cExcelUtils 对象找到第一个空行：m_lngNewRowNum = m_oXL.FindEmptyRow(m_oWorksheet)接下来，将传递给该方法的 cPerson 对象赋值给私有的用于不同的保存函数的模块级 cPerson 对象：Set m_oEmployee = Employee最后，触发一些保存函数，每个数据对象一个：SaveEmpDataSaveAddressDataSaveEquipmentDataSaveAccessDataSave 方法简单地将存储在 cPerson 对象（及其

36、内部的数据对象）中的数据转换到 EmpData 工作表中的单元格。在 cHRData 类模块中添加下列 Save 方法：Private Sub SaveEmpData()With m_oWorksheet.Cells(m_lngNewRowNum, 1).Value = m_oEmployee.ID.Cells(m_lngNewRowNum, 2).Value = m_oEmployee.FName.Cells(m_lngNewRowNum, 3).Value = m_oEmployee.MidInit.Cells(m_lngNewRowNum, 4).Value = m_oEmployee.

37、LName.Cells(m_lngNewRowNum, 5).Value = m_oEmployee.DOB.Cells(m_lngNewRowNum, 6).Value = m_oEmployee.SSN.Cells(m_lngNewRowNum, 7).Value = m_oEmployee.JobTitle.Cells(m_lngNewRowNum, 8).Value = m_oEmployee.Department.Cells(m_lngNewRowNum, 9).Value = m_oEmployee.EmailEnd WithEnd SubPrivate Sub SaveAddre

38、ssData()With m_oWorksheet.Cells(m_lngNewRowNum, 10).Value = m_oEmployee.Address.StreetAddress.Cells(m_lngNewRowNum, 11).Value = m_oEmployee.Address.StreetAddress2.Cells(m_lngNewRowNum, 12).Value = m_oEmployee.Address.City.Cells(m_lngNewRowNum, 13).Value = m_oEmployee.Address.State.Cells(m_lngNewRowN

39、um, 14).Value = m_oEmployee.Address.ZipCode.Cells(m_lngNewRowNum, 15).Value = m_oEmployee.Address.PhoneNumber.Cells(m_lngNewRowNum, 16).Value = m_oEmployee.Address.CellPhoneEnd WithEnd SubPrivate Sub SaveEquipmentData()With m_oWorksheet.Cells(m_lngNewRowNum, 17).Value = m_oEmployee.Equipment.PCType.

40、Cells(m_lngNewRowNum, 18).Value = m_oEmployee.Equipment.PhoneType.Cells(m_lngNewRowNum, 19).Value = m_oEmployee.Equipment.Location.Cells(m_lngNewRowNum, 20).Value = m_oEmployee.Equipment.FaxYNEnd WithEnd SubPrivate Sub SaveAccessData()With m_oWorksheet.Cells(m_lngNewRowNum, 21).Value = m_oEmployee.A

41、ccess.Building.Cells(m_lngNewRowNum, 22).Value = m_oEmployee.Access.NetworkLevel.Cells(m_lngNewRowNum, 23).Value = m_oEmployee.Access.RemoteYN.Cells(m_lngNewRowNum, 24).Value = m_oEmployee.Access.ParkingSpotEnd WithEnd Sub注意，用于获取 cPerson 对象的内部的 Address、Equipment、Access 对象数据的语法：m_oEmployee.Address.St

42、reetAddressm_oEmployee.Equipment.PCTypem_oEmployee.Access.Building在一个对象里使用另一个对象可以灵活地分类对象中的信息。管理向导创建两个类来帮助管理向导应用程序。第一个非常简单，包含每步的配置数据，接着创建一个类，包含这些“向导步骤”对象的集合，管理向导过程的操作。插入一个新的类模块，将其命名为 cStep，添加下列代码：Private m_iOrder As IntegerPrivate m_iPage As IntegerPrivate m_sCaption As StringPublic Property Get Orde

43、r() As IntegerOrder = m_iOrderEnd PropertyPublic Property Let Order(newOrder As Integer)m_iOrder = newOrderEnd PropertyPublic Property Get Page() As IntegerPage = m_iPageEnd PropertyPublic Property Let Page(newPage As Integer)m_iPage = newPageEnd PropertyPublic Property Get Caption() As StringCaptio

44、n = m_sCaptionEnd PropertyPublic Property Let Caption(newCaption As String)m_sCaption = newCaptionEnd PropertyHRWizard.xlms 工作簿包含一个名为 UFormConfig 的工作表，该工作表包含向导中每个步骤的信息。在这里，可以修改步骤的顺序或者插入一个新步骤。下表列出了 cStep 类的属性及其描述。表：cStep 属性类 描述Order 包含向导处理的顺序里步骤的位置Page 包含与多页控件中相应的页面一致的页号Caption显示在当前活动页控件中的文本下面，设置一个类来

45、管理向导中的步骤。在这个类中，将创建 cStep 对象的集合，用于追踪我们在处理过程中的哪一步以及共有多少步。插入一个新的类模块，将其命名为 cStepManager，添加下列模块级的变量声明：Dim m_oStep As cStepDim m_iNumSettings As IntegerDim m_iNumSteps As IntegerDim m_iCurrentPage As IntegerDim m_iPreviousPage As IntegerDim m_iNextPage As IntegerDim WithEvents m_oPreviousButton As MSForms

46、.CommandButtonDim WithEvents m_oNextButton As MSForms.CommandButtonDim m_oWorksheet As Worksheet通过接下来的一些 Integer 变量，cStep 对象 m_oStep 用于填充向导步骤的集合。告诉有多少步骤，每步有多少属性，基于用户在向导的位置追踪当前、下一个、前一个步骤。接下来，有两个设置为 MSForms.CommandButton 对象类型的变量，它们被声明为 WithEvents。我们让 cStepManager 类维护这些按钮的状态。WithEvents 声明来捕获它们的 Click 事

47、件，并在类里面执行操作。使用 Click 事件基于用户在向导中的位置决定是否启用按钮。在 cStepManager 中添加下面的代码：Public Property Get NumberOfSettings() As IntegerNumberOfSettings = m_iNumSettingsEnd PropertyPublic Property Let NumberOfSettings(newNum As Integer)m_iNumSettings = newNumEnd Property工作表属性 :获取/设置包含步骤信息的工作表Public Property Get Workshe

48、et() As WorksheetSet Worksheet = m_oWorksheetEnd PropertyPublic Property Set Worksheet(newWorksheet As Worksheet)Set m_oWorksheet = newWorksheetEnd PropertyPublic Property Get CurrentPage() As IntegerCurrentPage = m_iCurrentPageEnd PropertyPublic Property Let CurrentPage(newPage As Integer)m_iCurren

49、tPage = newPageEnd PropertyPublic Property Get PreviousPage() As IntegerPreviousPage = m_iCurrentPage - 1End PropertyPublic Property Get NextPage() As IntegerNextPage = m_iCurrentPage + 1End PropertyPublic Property Set PreviousButton(newPreviousBtn As MSForms.CommandButton)Set m_oPreviousButton = newPreviousBtnEnd PropertyPublic Property Set NextButton(newNextBtn As MSForms.CommandButton)Set m_oNextButton = newNextBtnEnd Property下表列出了 cStepManager 类的属性及其描述。表：cStepManager 属性类 描述NumberOfSettings 包含步骤配置工作表 UFormConfig 中的列数Worksheet 告诉类到哪里查找向导的每步的信息CurrentPage 在向导中存储当前步骤的值PreviousPage 基于