【转君以沫学习笔记】Excel VBA:以员工信息查询为例,学习操作窗体

时间:2024-03-03 10:08:51
基于“窗体”实现Excel表格内容的查询与修改。窗体,为使用者提供了较好的交互体验,使用者可以按照自己的需要实现数据的查询与修改,类似于数据库中的视图,用简洁明了的界面提供给使用者最关注的数据,屏蔽无用的数据信息。本文用一个查询员工信息的例子,简要介绍窗体的使用方法。

员工信息查询的例子

构造这样一个实例:某企业员工信息,包含姓名、性别、出生年月、政治面貌、学历学位等,每个员工的信息组合成一条记录,所有员工信息整合在一个Excel数据表中。样式如下:

图中信息均为随机虚构,请勿对号入座。

该表中只有10名员工信息,且员工信息相对较少,因此直观上看还是比较简洁的。但是设想一下,如果该企业有上千名员工,每个员工信息包含几十甚至上百个字段,那又会是如何一番景象呢?如果去寻找某位员工的某些信息,可能体验就不是那么友好了,需要好好擦亮双眼了。

每位员工应该都有一个唯一的员工号的,作为该员工信息记录的识别码(主键)。为了方便,我在这个例子中并未添加该字段信息,后续查找也是基于姓名来的,这要求姓名不能重名,否则不能查找到所有同名员工的信息。简要说明,能认识到这点足够了,问题很小很容易解决。

想要实现的功能

日常工作中,并不是所有的员工信息都会被经常用到,恰恰相反,经常用到的字段,一般是固定的几个。当表格信息较多时,去寻找某个记录的某些字段信息,是比较耗时耗力的。如果可以根据提供的某条记录的标识符,就可以依据该标识符快速展示出属于该记录的字段数据,并且能够屏蔽其他不关心的数据,那么体验就会大幅提升。

在该例中,我们基于窗体实现了快速信息查询及修改的功能。只需要提供员工姓名,就可快速查找出预设字段的该员工的数据信息。预设字段是我们关注的字段,比如性别、身份证号、政治面貌等。同时,预设字段也可以是表格中不存在的字段,比如年龄,年龄是随时间不断变化的,即使表格中有年龄数据,也可能由于更新不及时等原因,造成数据不准确。在窗体中添加年龄项,通过一定的运算,依据表格中的出生年月计算出年龄。这样,就可以通过姓名,随时查询每个员工的最新年龄信息。

查询示例如下图所示:

通过窗体显示数据信息,既可以屏蔽部分垃圾信息,使数据显示简洁友好,又可以形成固定的计算模板,获取原表格中不存在的字段信息,无需每次查询都要重新计算。除可以显示信息外,窗体也可以作为修改表格数据的渠道,只需要在查询数据显示框内,更新最新数据,提交修改即可。与直接在原表修改相比,因为界面简洁明了,不存在干扰信息,指向性明显,很大程度上降低了修改出错的可能性。

新建窗体

下面抛开具体实例,简要介绍窗体的基本使用方法。

一、新建一个窗体

在“开发工具”中点击“查看代码”,在弹出的代码编辑框中点击“插入”——“用户窗体”,可以看到如下界面:

其中,UserForm1即新建的窗体,只需要在上面添加窗体控件即可。我们可以在属性设置中给窗体修改一个名字,只需要将属性(姓名)设置为需要的名称,例如“test”,如图5所示:

二、窗体工具箱

默认新建窗体的时候,会同步打开“工具箱”,这里重点关注工具箱中的五个控件选项,依次是:

选定对象:功能类似于pdf文件中的小手,用来选中某个已建好的控件;

标签:图2中“姓名”“性别”等文字标签;

文字框:图2中“性别”“民族”等后面紧跟着用于填充具体值的文字框;

复合框:图2中“姓名”后面紧跟的带有下拉选项的复合框;

命令按钮:图2中“查询”和“提交修改”按钮。

三、新建窗体控件

新建一个窗体控件(标签、文本框、复合框等)的时候,只需要从工具箱中选中对应控件按钮,然后在窗体上单击即可,需要对控件进行简单设置。

  • 新建“标签”:设置标签属性,如图6:

重点修改3个参数值:

Caption:标签的文字内容,例如“姓名”;

Font:调节标签文字字体及字号;

TextAlign:字体对齐方式:左对齐、居中、右对齐。

其他参数请自行学习。

  • 新建“文本框”:如图7,重点修改3个参数值:

其中Font和TextAlign分别对应字体字号和文字对齐方式。

属性(名称):指定控件的名称,设置名称后,引用控件后就可以通过姓名.Value姓名.Name的方式分别引用控件的值和名称了。

  • 新建“复合框”:复合框的参数设置和文本框类似,重点也是(名称)、Font和TextAlign三个参数。复合框需要指定下拉选项的可选项值,一般通过VBA代码的形式,在窗体初始化的时候指定,后续会详细介绍。
  • 新建“命令按钮”:同“文本框”,需要设置(名称)、Font和TextAlign三个参数参数,其中属性(名称)在编写命令提交执行代码的时候,(名称)的值即点击命令按钮时执行的过程的过程名。

三、运行窗体

在窗体设计界面,直接按快捷键F5即可运行。

窗体代码控制

窗体建好后,如何实现窗体功能呢?自然是通过窗体后面编写的VBA代码实现的。

一、窗体初始化

回归到员工信息查询的实例。假如我们已经根据自身需要,建立了满足自身需求的窗体,如图8所示:

窗体建立好后,需要将其运行起来。只需要双击窗体空白处,即可跳转到窗体代码编辑页面,会自动生成一个窗体初始化过程:

Private Sub UserForm_Initialize()

End Sub

我们只需要在该过程中填写相关内容,完成窗体初始化。在员工信息查询实例中,初始化过程只需要执行一个操作,即复合框可选内容的填充。从功能角度来讲,就是读取所有员工姓名信息,提供姓名下拉框姓名选项。

实现过程非常的简单,只需要读取姓名存储到一个数组中,然后通过“复合框名称.List=数组”的方式完成。具体实现过程如下:

\'窗体初始化
Private Sub UserForm_Initialize()

    \'表格已使用区域行数
    Dim num As Integer
    num = ThisWorkbook.Sheets(2).Range("A1").CurrentRegion.Rows.Count

    \'姓名保存到数组中
    Dim Names As Variant
    Names = ThisWorkbook.Sheets(2).Range("C3:C" & num).Value

    \'窗体姓名复合框内容
    姓名.List = Names

End Sub

从以上语句中可以知道,员工姓名要保存在C列,且从第3行开始作为第一个员工信息(前两行是表头信息)。因为一开始不知道员工人数,因此通过读取表格总使用行数来间接确定员工人数。具体的实现方式是,新建一个动态数组,将员工姓名保存到动态数组中,然后将该数组赋值给复合框。

运行窗体,我们就可以看到如图9所示的效果。

二、添加控件动作

根据功能设计,窗体中有两个命令按钮:查询和提交修改。查询用来查找指定员工的信息,提交修改用来修改某位员工的某些字段值。

1.查询命令按钮

指定姓名可以通过下拉框选择,也可以手动输入,然后点击查询按钮,将该员工信息填充到相应的文本框中。双击查询按钮,会直接跳转到窗体代码编写页面,并自动生成一个子过程:

Private Sub SelectForm_Click()

End Sub

该过程的名称以“命令按钮名称”+“_Click()”的方式命名。这里我把查询命令按钮名称设置为SelectForm.

查询过程实现的功能:
-- 容错机制:未指定姓名、姓名不存在等情形下的错误提示;
-- 识别各文本框名称,并获取对应值。

如果设置ctl为文本框对象(TextBox),则文本框的名称和值分别通过“ctl.Name”和“ctl.Value”获取。

在这里,我构造了一个函数,用来查询指定姓名在工作簿中的行,具体过程如下:

\'查找指定姓名所在的行
Function FindNameRow()

    \'已使用行数
    Dim num As Integer
    num = ThisWorkbook.Sheets(2).Range("A1").CurrentRegion.Rows.Count

    \'依次测试每个单元格的值,如果是指定值,则返回指定值所在的行
    Dim rng As Range, row As Long
    For Each rng In ThisWorkbook.Sheets(2).Range("C3:C" & num)
        If rng.Value = 姓名.Value Then
            row = rng.row
            Exit For
        End If
    Next

    FindNameRow = row

End Function

该函数不需要参数。“姓名.Value”可以直接在函数内部引用。

姓名保存在C列,也就是第3列,这一点是固定的,用作模板要求,不能变更。因为一个员工的所有信息储存在同一行内,只要获取到该员工姓名所在的行,也就知道了该员工所有信息所在的行。对于其他字段,同一个员工的信息肯定和姓名在同一行,只需要定位该字段的列,就可以定位出此字段的值在工作表中的位置。因此,我同时定义了一个函数,用来查找指定字段名所在的列,具体如下:

\'查找各字段所在的列(数值,第几列)
Function FindItemColumn(item)

    \'已使用区域列数
    Dim num As Integer
    num = ThisWorkbook.Sheets(2).Range("A1").CurrentRegion.Columns.Count

    \'依次测试包含字段名的单元格区域,如果是指定字段名,则返回所在的列(数值,第几列)
    Dim rng As Range, col As Integer
    For Each rng In ThisWorkbook.Sheets(2).Range("A2", ThisWorkbook.Sheets(2).Cells(2, num))
        If rng.Value = item Then
            col = rng.column
            Exit For
        End If
    Next

    FindItemColumn = col

End Function

该函数参数为字段名,该字段名通过窗体文本框名称给定(设置窗体的时候,将文本框名称设置为该文本框需要显示的内容对应的字段名称)。例如显示性别的文本框名称设置为“性别”,员工信息工作表中储存性别信息的字段名称也为“性别”,这两个名称需要保持一致。函数会找到“性别”这个字段所在的列(第几列),通过人名确定行,由此确定了该员工性别所在的行和列,也就可以提取到对应的值了。

给窗体中“性别”文本框赋值,只需要通过“性别.Value=sht.Cells(row,col).Value”,其中,sht代表保存员工信息的工作表对象,row和col分别是通过上述函数获取的行和列。

还有最后一个问题,窗体中文本框可能有多个,我们不可能挨个指定文本框名称去获取对应的值。因此,这里我引用了Control对象,通过对窗体对象的遍历,寻找到所有类型为TextBox(文本框)的控件,获取该控件的名称,用该名称去匹配工作表中所有字段名(前面提到的将文本框名称和对应字段名设置为一致的原因),找到完全匹配的字段名所在的列,也就是该文本框需要显示的值所在的列,行通过姓名确定。行和列确定后,该文本框的值就可以直接获取了。

完整实现代码如下:

\'点击“查询”按钮后将要执行的操作
Private Sub SelectForm_Click()

    \'处理未指定姓名的查询
    If Len(姓名.Value) = 0 Then
        MsgBox ("请先指定需要查询的人员姓名!")
        Exit Sub
    End If

    \'以姓名作为查询条件,获取指定姓名的人员信息所在的行
    Dim row As Long
    row = FindNameRow()

    \'处理查询不存在的情形
    If row < 3 Then
        MsgBox ("无法查询到姓名为" & """" & 姓名.Value & """" & "的人员信息,请确认后再试!")
        Exit Sub
    End If

    \'item指定要填充的字段名,col代表查找该字段所在的列(数字,第几列)
    Dim item As String, col As Integer
    Dim ctl As Control

     \'判断窗体中文本框内容是否有更改,如果有更改则更新工作簿对应字段值
    For Each ctl In WorkerInfo.Controls
        \'如果是窗体文本框
        If TypeName(ctl) = "TextBox" Then
            col = FindItemColumn(ctl.Name)
            \'如果col <> 0,表示能够在工作簿中查询到该字段,能找到才能修改
            If col <> 0 Then
                If ctl.Name <> "所在部门" Then
                    ctl.Value = ThisWorkbook.Sheets(2).Cells(row, col).Value
                Else
                    ctl.Value = ThisWorkbook.Sheets(2).Range(ThisWorkbook.Sheets(2).Cells(row, col), ThisWorkbook.Sheets(2).Cells(row, col)).MergeArea.Cells(1, 1).Value
                End If
            End If

        End If

    Next

    \'根据出生年月计算并显示年龄
    Dim date1 As String, date2 As String
    date1 = 出生年月.Value
    date2 = Application.Text(Date, "yyyy/mm/dd")
    年龄.Value = Application.Evaluate("=DATEDIF(""" & date1 & """,""" & date2 & """, ""y"") ")

End Sub

 

再回头看一下容错机制,首先明确一点,“姓名.Value”的类型是String,查询姓名的时候,有可能姓名没有输入,这时候“姓名.Value”的长度为0,只需要检测其长度,即可实现错误识别。还有一种错误是,输入的姓名不在员工信息表中,那么自定义的函数FindNameRow返回值为0,通过检测函数返回值,即可识别错误。

2.提交修改按钮

指定姓名点击查询后,更改该员工某个字段值,或者直接指定姓名,填写需要修改的字段值,然后点击提交修改,都可以完成该功能。

该部分实现起来与查询有很多相似的地方,基本思想是核对每个文本框值与工作表中对应字段值是否一致,如果不一致,我们认为是进行了修改,只需要将工作表中对应字段值修改。这里有几个问题:

-- 容错机制:如果没有指定姓名或者姓名不存在,处理方式同查询;
-- 因窗体文本框值类型为String,对于工作簿中的类型为Date或者Double等类型的数据时,需要统一数据类型后再进行比较,并且修改工作表值的时候,也要按照工作表字段类型进行修改;
-- 如果窗体文本框中的值格式不正确(非精确判断,提供一种基本的解决思路,只针对明显错误,例如日期填写为“男”),则进行错误提示;
-- 依旧引用Control对象,获取文本框值和工作表中对应字段值,进行比较,如果有差异,则将文本框值(类型调整后)更新到工作表中。

该部分完整代码如下:

\'点击“提交修改”按钮后执行的操作
Private Sub SubmitModify_Click()


    \'如果没有指定员工姓名,则弹窗提醒并退出执行
    If Len(姓名.Value) = 0 Then
        MsgBox "请先指定员工姓名后提交修改!"
        Exit Sub
    End If

    \'以姓名作为查询条件,获取指定姓名的人员信息所在的行
    Dim row As Long
    row = FindNameRow()

    \'处理查询不存在的情形
    If row < 3 Then
        MsgBox ("无法查询到姓名为" & """" & 姓名.Value & """" & "的人员信息,请确认后再试!")
        Exit Sub
    End If

    \'ctl代表窗体中文本框对象,col代表查找该字段所在的列(数字,第几列)
    Dim ctl As Control
    Dim col As Integer

    \'ctl_value用于修正数据类型,msg构造弹窗修改提示信息
    Dim ctl_value As String, msg As String

    \'判断窗体中文本框内容是否有更改,如果有更改则更新工作簿对应字段值
    For Each ctl In WorkerInfo.Controls

        \'如果是窗体文本框
        If TypeName(ctl) = "TextBox" Then
            col = FindItemColumn(ctl.Name)

            \'如果col <> 0,表示能够在工作簿中查询到该字段,能找到才能修改
            If col <> 0 Then

                \'窗体文本框值的类型均为字符串,遇到其他类型数据需要更改数据类型
                If TypeName(ThisWorkbook.Sheets(2).Cells(row, col).Value) = "Date" Then
                    \'如果出错,则报错
                    On Error GoTo ERROR_1
                    ctl_value = CDate(ctl.Value)
                    \'如果上句不出错,跳过报错语句
                    GoTo CONTINUE_DO
ERROR_1:
                    MsgBox """" & ctl.Name & """" & "日期格式不正确,请输入参照“2019/01/01”的格式输入!"
                    Err.Clear
                    GoTo NEXT_DO

                ElseIf TypeName(ThisWorkbook.Sheets(2).Cells(row, col).Value) = "Double" Then
                    \'如果不是数值型字符串,则执行报错语句
                    If IsNumeric(ctl.Value) Then
                        ctl_value = Val(ctl.Value)
                        GoTo CONTINUE_DO
                    Else
                        GoTo ERROR_2
                    End If

                    \'On Error GoTo ERROR_2
                    \'ctl_value = Val(ctl.Value)
                    \'GoTo CONTINUE_DO

ERROR_2:
                    MsgBox """" & ctl.Name & """" & "请输入数字!"
                    Err.Clear
                    GoTo NEXT_DO
                Else
                    ctl_value = ctl.Value
                End If

CONTINUE_DO:
                \'如果窗体文本框中值有更改(工作簿中的值和窗体文本框值不同)
                If ThisWorkbook.Sheets(2).Cells(row, col).Value <> ctl_value And ctl.Name <> "所在部门" Then
                    \'弹窗提示是否确认修改
                    msg = "确定将" & """" & ctl.Name & """" & "" & """" & ThisWorkbook.Sheets(2).Cells(row, col).Value & """" & "修改为" & """" & ctl.Value & """" & "吗?"
                    If MsgBox(msg, vbYesNo) = vbYes Then
                        \'不是“所在部门”这个字段(合并单元格需要单独处理)
                        If ctl.Name <> "所在部门" Then
                            ThisWorkbook.Sheets(2).Cells(row, col).Value = ctl_value
                        Else
                            ThisWorkbook.Sheets(2).Range(ThisWorkbook.Sheets(2).Cells(row, col), ThisWorkbook.Sheets(2).Cells(row, col)).MergeArea.Cells(1, 1).Value = ctl_value
                        End If
                    End If
                End If

            End If

        End If

NEXT_DO:

    Next

    \'保存更改
    ThisWorkbook.Save
    \'更新查询结果(重新执行一次查询)
    Call SelectForm_Click

End Sub

 

针对格式不正确的填充数据,纠错机制借助了好几处的goto语句实现,比较啰嗦,也许有更好的实现方式,欢迎指正。

到目前为止,窗体的基本设置就完成了,现在可以在窗体设计界面按F5运行查看了。

设置查询入口

我们并不希望每次运行都要先打开“开发工具”——“查看代码”,那么,只需要再添加一个入口按钮就可以了。

我们在“开发工具”——“查看代码”——“ThisWorkbook”中添加如下过程:

Public Sub ShowForm()
    With WorkerInfo
        .startupposition = 0
        .Top = 100
        .Left = 200
        .Show
    End With
End Sub

该过程指定了窗体的初始位置。然后将员工信息表保存在工作簿的第二张sheet表中,第一张sheet表设置一个窗体查询入口,如图10所示:

点击“开始查询”就可以弹出窗体了。