基于Excel VBA的考試管理系統設計

蔣 勇

(揚州職業大學, 江蘇 揚州 225009)

考試的編排是學校教學管理中的常規工作。以往的考試編排都是教學管理人員在Excel表格中手工輸入各種考試信息,不但數據量大,而且工作效率低下,有時還會出現監考人員在同一場次考試中重復編排的問題。VBA是VB語言的一個子集, VBA內嵌在Office的各應用程序中,如Word、 Excel、 Access等,其程序的編寫和運行都以這些程序為平臺。VBA在Excel中的應用最為廣泛,可以讓復雜的工作簡易化,減少不必要的重復性工作,提高工作效率。

1 設計思路

本系統以Excel程序作為系統平臺。在新建的Excel工作薄中新建五個表格,分別命名為“監考安排表”“監考安排表總表”“教師監考表”“學生監考表”“基本設置表”。

“監考安排表”的功能是在此表中完成同一場次一門或多門考試課程的監考編排?！氨O考安排表總表”是存放不同場次同一階段考試的匯總表,它是生成教師和班級監考表的數據總表?！敖處煴O考表”和“學生監考表”是由“監考安排表總表”自動生成的教師個人和班級的監考記錄?！盎驹O置表”存放編排監考表必要的基本信息,如班級名稱和班級人數、教室編號和座位數、課程名稱、教師編號和可以參加監考的教師姓名等。其流程圖如圖1所示[1-2]。

圖1 系統生成流程

2 同一場次考試信息的模塊設計

同一場次考試信息模塊是考試編排的第一步,通過選擇考試的時間、教室、班級和課程一次性生成同一門課程的所有考試班級的考試安排表,并將其傳送到“監考安排表”中。在日期控件和四個文本框中確定考試的時間后,再選定考試的課程,課程的選擇只能在復合框選擇一門課程,該功能模塊只能一次安排同一門課程的多個班級,如需要安排其他課程,則再次運行該模塊即可,如圖2所示。

圖2 考試時間、課程、班級、考場選定模塊

其代碼如下[3]。

Private Sub CommandButton1-Click()

Dim i%, j%, Acolm%

t=0

If ComboBox1.Value="" Then

MsgBox "請選擇考試課程"

Else

For i=0 To ListBox1.ListCount-1

Acolm=Application.CountA(Sheets("監考安排表").Range("A2:A65536"))+1

For j=0 To ListBox2.ListCount-1

If ListBox1.Selected(i) Then

If ListBox2.Selected(j) Then

With Sheets("監考安排表")

.Cells(Acolm+1, 1)="'" & Left(DTPicker1.Value, 8)

.Cells(Acolm+1, 2)="'" & Right(DTPicker1.Value, 3)

.Cells(Acolm+1, 3)=TextBox1+":"+TextBox2+"—"+TextBox3+":"+TextBox4

.Cells(Acolm+1, 4)=ListBox1.List(i, 0)

.Cells(Acolm+1, 5)=ListBox1.List(i, 1)

.Cells(Acolm+1, 6)=ComboBox1.Value

.Cells(Acolm+1, 7)=ListBox2.List(j, 0)

End With

End If

End If

Next

Next

End If

End Sub

3 確定監考教師模塊的設計

第二個功能模塊是在“監考安排表”中確定同一場次的監考教師。本系統有兩種方式來確定監考教師,一是由教學管理人員人為選定,另外一種方式是系統隨機生成監考人員。在同一場次的監考教師確定完成后,就可將其內容傳送到“監考安排表總表”后再安排其他場次考試的編排。重復上述操作,直到同一階段所有考試課程的編排完成[4-5]。

3.1 人為指定監考教師代碼

本模塊先安排同一場次需要監考教師的人數的一半,在可參與監考教師的列表框中選擇參與監考的教師。選定后點擊“手動安排監考1教師”按鈕,所選教師將自動填充到監考安排表的監考教師列表中,列表框中已選過的教師將自動移除。然后再在列表框中選擇另一半監考教師,點擊“手動安排監考2教師”按鈕即可。

其代碼如下:

Private Sub CommandButton3-Click()

Dim i As Integer

n=ListBox1.ListCount-1

For i=0 To n

If ListBox1.Selected(i)=True Then

With Sheets("監考安排表")

.Cells(.Range("H65536").End(xlUp).Row+1, 8)=ListBox1.List(i)

End With

End If

Next

'------將已經導入的監考老師姓名從列表框中移除

For j=ListBox1.ListCount-1 To 0 Step-1

If ListBox1.Selected(j)=True Then

ListBox1.RemoveItem (j)

End If

Next

Call 統計未安排監考教師數

End Sub

3.2 系統隨機生成監考教師

該模塊是在“監考安排表”中對同一場次的監考教師進行隨機安排,要事先在“基本設置”表中確定能夠參加本次監考的教師姓名,并重新編號,將不能參加監考的教師姓名移動到旁邊單元格中。

由于每個考場正常需要2名監考教師,系統根據“監考安排表”同一場次安排的考場數,產生2倍于考場數的隨機數,產生的每一個隨機數和“基本設置”表中可參加監考教師列前的序號進行對比,如果隨機數和序號相同則將這位教師的姓名復制到“監考安排表”的“監考教師1”列中,對“監考安排表”中的H列“監考教師1”和I列“監考教師2”進行隨機分配,為了減少代碼的復雜性,先采用了將同一場次監考教師先全部生成在“監考教師1”中,然后再將生成和監考教師的后面一半通過剪切和復制方法,將姓名復制到“監考教師2”中。

其代碼如下：

Private Sub CommandButton2-Click()

Dim rngs As Range

Dim xuhao, k, i, s As Integer

Dim arr() As String

grow=Sheets("基本設置表").Range("G65536").End(xlUp).Row '---返回可參加監考教師列人數

arow=Sheets("監考安排表").Range("A65536").End(xlUp).Row '--返回監考考場數

k=0

Randomize (Time)

With Sheets("監考安排表")

s=(arow-1)*2

Do While k < s

xuhao=Int((grow-1) * Rnd)+1 '---根據基本設置表中可監考教師人數產生隨機數

For Each rngs In Sheets("基本設置表").Range("G2:G" & grow) '------遍歷基本設置表教師序號列

If rngs=xuhao Then '------產生隨機數和基本設置表中可監考教師的序號進行比對如果相同

.Cells(k+2, 8)=rngs.Offset(, 1) '----基本設置表中可監考教師的姓名賦給監考安排表從第2行8列開始

End If

Next

k=k+1

Loop

'---------------將一列監考老師改為兩列監考老師-----------------

hrow=Sheets("監考安排表").Range("H65536").End(xlUp).Row

Range("H" & ((hrow-1)/2+2) & ":" & "H" & hrow).Select

Selection.Cut

Range("I2").Select

ActiveSheet.Paste

End With

End Sub

4 教師監考表和班級考試安排表功能模塊設計

第三模塊是在“監考安排表總表”基礎上,生成教師個人監考記錄和班級的考試安排表。

以往教師或學生查閱監考安排時,一般都是看監考總表,由于表格中信息沒有規律可循,有時會漏看本人的信息,造成教學事故。本模塊為了方便教師和學生查閱教師本人或學生本班級監考信息,將全部的監考場次在“教師監考表”和“班級監考表”中分別形成新記錄,然后分別按教師姓名和班級名稱的字母讀音順序排列,記錄中將一些和監考任務無關的信息去除。教師的監考表只有教師的姓名、監考時間、教室、班級和課程這5個信息,教師本人所有監考場次都集中在一起,在“教師監考表”就可以查看本人所監考的所有場次。班級的監考表也是同樣原理。點擊“監考安排總表”中“生成教師監考表”按鈕，即可在“教師監考表”中生成以教師姓名排序的監考記錄。

其代碼如下(以教師監考表為例):

Private Sub CommandButton1-Click()

Application.ScreenUpdating=False

Dim zbArow, jsArow As Integer

Dim rngI, rangJ As Range

zbArow=Sheets("監考安排總表").Range("A65536").End(xlUp).Row

With Sheets("教師監考表")

For Each rngI In Sheets("監考安排總表").Range("I2:I" & zbArow)

Acol=Application.CountA(Sheets("教師監考表").Range("A1:A65536"))+1

.Cells(Acol, 1)=rngI

.Cells(Acol, 2)=rngI.Offset(,-7)

.Cells(Acol, 3)=rngI.Offset(,-6)

.Cells(Acol, 4)=rngI.Offset(,-4)

.Cells(Acol, 5)=rngI.Offset(,-2)

Next

For Each rngJ In Sheets("監考安排總表").Range("J2:J" & zbArow)

Acol=Application.CountA(Sheets("教師監考表").Range("A1:A65536"))+1

.Cells(Acol, 1)=rngJ

.Cells(Acol, 2)=rngJ.Offset(,-8)

.Cells(Acol, 3)=rngJ.Offset(,-7)

.Cells(Acol, 4)=rngJ.Offset(,-5)

.Cells(Acol, 5)=rngJ.Offset(,-3)

Next

jsArow=.Range("A65536").End(xlUp).Row

.Sort.SortFields.Clear

.Sort.SortFields.Add Key:=Range("A2:A" & jsArow),-

SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

.Sort.SortFields.Add Key:=Range("B2:B" & jsArow),-

SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

End With

'-------------------對生成記錄按教師姓名進行排序------------------------

With Worksheets("教師監考表").Sort

.SetRange Range("A1:E" & jsArow):.Header=xlYes:.MatchCase=False

.Orientation=xlTopToBottom:.SortMethod=xlPinYin:.Apply

End With

5 結語

教學管理人員使用本系統來編排監考表,工作效率有了很大提高,減少了大量重復信息的輸入,節省了編排時間。自動生成的教師監考表和班級監考表,更方便教師和學生查閱。