基于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.

【通聯編輯：王力】