基于Excel VBA的考勤表自动生成程序

崔子健

摘要：学校各班进行周末留校学生统计时，普遍存在统计过程烦琐、不易修改及考勤表信息错漏率高等问题。为解决这类问题，使用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 <= Boys Then‘防止选出花名册区域；其中Boys变量为程序数出的男生总人数

If Current.Interior.Color = RGB（102， 255， 51） Then

Current.Interior.ColorIndex = xlNone

Else

Current.Interior.Color = RGB（102， 255， 51）

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 <= 14 Then ‘判断留校人数是否超过考勤表单列容纳人数

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语言编写程序、解决问题，必将助推学校各项工作迈上新的台阶。

参考文献：

[1] 罗刚君.Excel VBA程序开发自学宝典[M].北京：电子工业出版社，2014.

[2] 石晓玲，杨立功.基于ExcelVBA及函数的成绩上报单的制作[J].泰州职业技术学院学报，2011，11（5）：40-42.

[3] 曾洋.EXCEL VBA在数据表处理中的应用[J].重庆电子工程职业学院学报，2010，19（3）：161-163.

【通联编辑：王力】