基于Excel VBA的考勤表自动生成程序
作者：崔子健
来源：《电脑知识与技术》2019年第08期
        摘要：学校各班进行周末留校学生统计时，普遍存在统计过程烦琐、不易修改及考勤表信息错漏率高等问题。为解决这类问题，使用Excel VBA语言编写了考勤表自动生成程序，实现了留校统计过程的简化及考勤表制表过程的全自动化，有效提高了考勤统计工作的效率及规范性。
        关键词：考勤表；统计界面；自动生成；算法选择；Excel；VBA
        中图分类号：TP311 文献标识码：A
        文章编号：1009-3044（2019）08-0040-02
        1 程序设计背景
        笔者所在学校实行双周自愿留校制度，每次留校前要求各班统计留校学生并分别打印上交
男、女生考勤表。惯例上，各班负责人需将申请留校的学生及其信息统计后逐个键入考勤表。这一过程的突出问题表现为填表效率低、易忘记修改周数，且有学生欲变更留校时不便于改动。若能依托各班教室内配备的多媒体计算机，直接在计算机上完成统计工作并把制表工作交由计算机自动完成，则可大幅度优化工作流程、提高工作速度与灵活性，减轻各班负责人的工作负担，同时增强考勤表的规范性，具有一定的现实意义。为此，设计了基于Excel VBA语言的考勤表自动生成程序。
        2 程序分析
        本程序需要整合留校学生统计与考勤表自动生成这两项功能，分别由统计程序与考勤表生成程序实现。班级花名册被嵌入程序所在工作表作为统计程序界面（图1），使用者只需在多媒体的触摸屏上单击欲留校者的姓名，相应单元格即被填充颜，表示已被记录；当学生临时决定取消留校时，只需再次单击对应单元格即可。统计完毕后，在操作界面上填写并确认周数等信息，按下“生成考勤表”按钮，考勤表生成程序即根据统计信息自动输出格式统一的考勤名单。
        3 程序设计
        3.1 统计程序的设计
        统计程序的记录功能由工作表SelectionChange事件触发。此事件中，程序仅仅完成单元格填充颜的更改，而不涉及留校人数计算等其他工作，可提高执行速度。由于针对男、女生的事件代码只存在调用列号之间的差异，故下面只给出针对男生一列的程序代码：
        Private Sub Worksheet_SelectionChange（ByVal Current As Range）
        Dim C As Integer， R As Integer
        Sheets（"花名册（主程序）"）.Activate
        C = Current.Column ‘获取当前选区的行、列号
        R = Current.Row
        Select Case C
        Case Is = 2 ‘选择男生所在列时继续执行
        If 1 < R And R
        If Current.Interior.Color = RGB（102， 255， 51） Then
        Current.Interior.ColorIndex = xlNone
        Else
        Current.Interior.Color = RGB（102， 255， 51）excel自动生成排名
        End If
        ActiveSheet.[L3] = Current.Text ‘在单元格中用大号字体显示所选学生，该学生看到后就座
        ActiveSheet.[Q3] = "√"
        ActiveSheet.[L10] = "请坐下！"
        Else
        MsgBox "请点击要改动的学生姓名！"， vbExclamation， "选择范围错误"
        End If
        End Sub
        3.2 考勤表生成程序的设计
        3.2.1 数据整理传递算法的选择
        由于所统计的学生在縱列上往往是不连续的（参见图1），因此将以填充颜方式记录的学生信息传递到模板考勤表中时，不能直接使用粘贴方法，而需要设计一定的算法进行数据的连续化整理再进行传递。最初设计时，提出了如下两种可行的数据整理传递算法：
        为选出运行效率最高的算法，针对这两种算法分别进行了运行耗时测试。由于在VBA开发环境中无法直接对单次运行精确计时，本测试采用了While循环将每种算法重复执行多次并通过MsgBox函数返回精确到秒的总运行时间。其测试结果如表1所示。
        由测试结果可见，算法B运行效率远高于算法A。又考虑到算法B可减少工作表切换时的
闪屏现象，故程序选择算法B进行数据的整理与传递。
        3.2.2 界面与代码设计
        考勤表生成程序的操作界面如图3所示。使用者确认考勤表关键信息后，按下按钮程序即生成考勤表。文本框内的信息在首次输入后将保持不变，周号信息可通过SpinButton控件快速更改，方便同一班级连续使用。
        下面展示的是程序核心控件“生成考勤表”按钮的Click事件对应代码。同样地，由于整理传递男、女生信息的算法代码十分相似，故仅给出处理男生信息的相应代码。
        Private Sub CommandButton1_Click（）
        Dim ConfB As Integer， ConfG As Integer ‘变量用于确定留校的男、女生人数
        Dim BoyName（） As String， BoyDorm（） As String， GirlName（） As String， GirlDorm（） As String
        ‘声明算法中调用的动态字符串数组
        For BC = 1 To Boys – 1 ‘进入算法B第一个循环结构
        If ActiveSheet.Cells（1 + BC， 2）.Interior.Color = RGB（102， 255， 51） Then
        ConfB = ConfB + 1 ‘數出留校人数
        ReDim Preserve BoyName（1 To ConfB） ‘以Preserve关键字调整数组长度
        ReDim Preserve BoyDorm（1 To ConfB）
        BoyName（ConfB） = ActiveSheet.Cells（1 + BC， 2）.Text ‘下面将学生信息整理到数组中
        BoyDorm（ConfB） = ActiveSheet.Cells（1 + BC， 3）.Text
        Else
        End If
        Next BC
        Sheets（"男生考勤表"）.Activate
        ActiveSheet.Range（ActiveSheet.[B5]， ActiveSheet.[C18]）.ClearContents ‘清除原有内容
        ActiveSheet.Range（ActiveSheet.[I5]， ActiveSheet.[J18]）.ClearContents
        ActiveSheet.[A1] = TextSchoolYear.Text + "学年第" + TextTerm.Text + "学期第" + TextWeek.Text + "周周末延时服务留校学生考勤表（男生）" ‘据文本框内信息统一标题格式
        ActiveSheet.[A2] = "班级： " + TextGrade.Text + " 级 " + TextClass.Text + " 班"
        If ConfB
        For Bfill = 1 To ConfB ‘进入算法B第二个循环结构
        ActiveSheet.Cells（4 + Bfill， 2） = BoyName（Bfill） ‘下面传递学生信息
        ActiveSheet.Cells（4 + Bfill， 3） = BoyDorm（Bfill）
        Next Bfill
        Else
        For Bfill = 1 To 14
        ActiveSheet.Cells（4 + Bfill， 2） = BoyName（Bfill）
        ActiveSheet.Cells（4 + Bfill， 3） = BoyDorm（Bfill）
        Next Bfill
        For Bfill = 15 To ConfB
        ActiveSheet.Cells（Bfill - 10， 9） = BoyName（Bfill）
        ActiveSheet.Cells（Bfill - 10， 10） = BoyDorm（Bfill）
        Next Bfill
        End If
        ActiveWorkbook.Save ‘实现考勤表的自动保存
        MsgBox "延时服务考勤表已成功生成！"， vbOKOnly， "生成完毕"
        End Sub
        4 程序运行效果
        使用图1、图2中所示信息，程序自动生成的留校考勤表（女生）如图4所示。
        可以看到，程序生成的考勤表格式规范、信息准确。实际投入使用以来，本程序显著提高了工作质量，获得了学校好评。
        5 结束语
        考勤表自动生成程序的实现，克服了一直以来各班手动填写考勤表的种种弊端，有效实现了考勤表制表过程的简化、自动化与规范化，推动了班级管理工作的进步，体现了Excel VBA语言在教育信息化时代的重要作用。掌握并善于使用Excel VBA语言编写程序、解决问题，必将助推学校各项工作迈上新的台阶。