基于Excel VBA的考試管理系統(tǒng)設(shè)計(jì)

蔣 勇

(揚(yáng)州職業(yè)大學(xué), 江蘇 揚(yáng)州 225009)

考試的編排是學(xué)校教學(xué)管理中的常規(guī)工作。以往的考試編排都是教學(xué)管理人員在Excel表格中手工輸入各種考試信息,不但數(shù)據(jù)量大,而且工作效率低下,有時(shí)還會(huì)出現(xiàn)監(jiān)考人員在同一場(chǎng)次考試中重復(fù)編排的問(wèn)題。VBA是VB語(yǔ)言的一個(gè)子集, VBA內(nèi)嵌在Office的各應(yīng)用程序中,如Word、 Excel、 Access等,其程序的編寫和運(yùn)行都以這些程序?yàn)槠脚_(tái)。VBA在Excel中的應(yīng)用最為廣泛,可以讓復(fù)雜的工作簡(jiǎn)易化,減少不必要的重復(fù)性工作,提高工作效率。

1 設(shè)計(jì)思路

本系統(tǒng)以Excel程序作為系統(tǒng)平臺(tái)。在新建的Excel工作薄中新建五個(gè)表格,分別命名為“監(jiān)考安排表”“監(jiān)考安排表總表”“教師監(jiān)考表”“學(xué)生監(jiān)考表”“基本設(shè)置表”。

“監(jiān)考安排表”的功能是在此表中完成同一場(chǎng)次一門或多門考試課程的監(jiān)考編排?！氨O(jiān)考安排表總表”是存放不同場(chǎng)次同一階段考試的匯總表,它是生成教師和班級(jí)監(jiān)考表的數(shù)據(jù)總表。“教師監(jiān)考表”和“學(xué)生監(jiān)考表”是由“監(jiān)考安排表總表”自動(dòng)生成的教師個(gè)人和班級(jí)的監(jiān)考記錄?！盎驹O(shè)置表”存放編排監(jiān)考表必要的基本信息,如班級(jí)名稱和班級(jí)人數(shù)、教室編號(hào)和座位數(shù)、課程名稱、教師編號(hào)和可以參加監(jiān)考的教師姓名等。其流程圖如圖1所示[1-2]。

圖1 系統(tǒng)生成流程

2 同一場(chǎng)次考試信息的模塊設(shè)計(jì)

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

圖2 考試時(shí)間、課程、班級(jí)、考場(chǎng)選定模塊

其代碼如下[3]。

Private Sub CommandButton1-Click()

Dim i%, j%, Acolm%

t=0

If ComboBox1.Value="" Then

MsgBox "請(qǐng)選擇考試課程"

Else

For i=0 To ListBox1.ListCount-1

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

For j=0 To ListBox2.ListCount-1

If ListBox1.Selected(i) Then

If ListBox2.Selected(j) Then

With Sheets("監(jiān)考安排表")

.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 確定監(jiān)考教師模塊的設(shè)計(jì)

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

3.1 人為指定監(jiān)考教師代碼

本模塊先安排同一場(chǎng)次需要監(jiān)考教師的人數(shù)的一半,在可參與監(jiān)考教師的列表框中選擇參與監(jiān)考的教師。選定后點(diǎn)擊“手動(dòng)安排監(jiān)考1教師”按鈕,所選教師將自動(dòng)填充到監(jiān)考安排表的監(jiān)考教師列表中,列表框中已選過(guò)的教師將自動(dòng)移除。然后再在列表框中選擇另一半監(jiān)考教師,點(diǎn)擊“手動(dòng)安排監(jiān)考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("監(jiān)考安排表")

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

End With

End If

Next

'------將已經(jīng)導(dǎo)入的監(jiān)考老師姓名從列表框中移除

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

If ListBox1.Selected(j)=True Then

ListBox1.RemoveItem (j)

End If

Next

Call 統(tǒng)計(jì)未安排監(jiān)考教師數(shù)

End Sub

3.2 系統(tǒng)隨機(jī)生成監(jiān)考教師

該模塊是在“監(jiān)考安排表”中對(duì)同一場(chǎng)次的監(jiān)考教師進(jìn)行隨機(jī)安排,要事先在“基本設(shè)置”表中確定能夠參加本次監(jiān)考的教師姓名,并重新編號(hào),將不能參加監(jiān)考的教師姓名移動(dòng)到旁邊單元格中。

由于每個(gè)考場(chǎng)正常需要2名監(jiān)考教師,系統(tǒng)根據(jù)“監(jiān)考安排表”同一場(chǎng)次安排的考場(chǎng)數(shù),產(chǎn)生2倍于考場(chǎng)數(shù)的隨機(jī)數(shù),產(chǎn)生的每一個(gè)隨機(jī)數(shù)和“基本設(shè)置”表中可參加監(jiān)考教師列前的序號(hào)進(jìn)行對(duì)比,如果隨機(jī)數(shù)和序號(hào)相同則將這位教師的姓名復(fù)制到“監(jiān)考安排表”的“監(jiān)考教師1”列中,對(duì)“監(jiān)考安排表”中的H列“監(jiān)考教師1”和I列“監(jiān)考教師2”進(jìn)行隨機(jī)分配,為了減少代碼的復(fù)雜性,先采用了將同一場(chǎng)次監(jiān)考教師先全部生成在“監(jiān)考教師1”中,然后再將生成和監(jiān)考教師的后面一半通過(guò)剪切和復(fù)制方法,將姓名復(fù)制到“監(jiān)考教師2”中。

其代碼如下：

Private Sub CommandButton2-Click()

Dim rngs As Range

Dim xuhao, k, i, s As Integer

Dim arr() As String

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

arow=Sheets("監(jiān)考安排表").Range("A65536").End(xlUp).Row '--返回監(jiān)考考場(chǎng)數(shù)

k=0

Randomize (Time)

With Sheets("監(jiān)考安排表")

s=(arow-1)*2

Do While k < s

xuhao=Int((grow-1) * Rnd)+1 '---根據(jù)基本設(shè)置表中可監(jiān)考教師人數(shù)產(chǎn)生隨機(jī)數(shù)

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

If rngs=xuhao Then '------產(chǎn)生隨機(jī)數(shù)和基本設(shè)置表中可監(jiān)考教師的序號(hào)進(jìn)行比對(duì)如果相同

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

End If

Next

k=k+1

Loop

'---------------將一列監(jiān)考老師改為兩列監(jiān)考老師-----------------

hrow=Sheets("監(jiān)考安排表").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 教師監(jiān)考表和班級(jí)考試安排表功能模塊設(shè)計(jì)

第三模塊是在“監(jiān)考安排表總表”基礎(chǔ)上,生成教師個(gè)人監(jiān)考記錄和班級(jí)的考試安排表。

以往教師或?qū)W生查閱監(jiān)考安排時(shí),一般都是看監(jiān)考總表,由于表格中信息沒(méi)有規(guī)律可循,有時(shí)會(huì)漏看本人的信息,造成教學(xué)事故。本模塊為了方便教師和學(xué)生查閱教師本人或?qū)W生本班級(jí)監(jiān)考信息,將全部的監(jiān)考場(chǎng)次在“教師監(jiān)考表”和“班級(jí)監(jiān)考表”中分別形成新記錄,然后分別按教師姓名和班級(jí)名稱的字母讀音順序排列,記錄中將一些和監(jiān)考任務(wù)無(wú)關(guān)的信息去除。教師的監(jiān)考表只有教師的姓名、監(jiān)考時(shí)間、教室、班級(jí)和課程這5個(gè)信息,教師本人所有監(jiān)考場(chǎng)次都集中在一起,在“教師監(jiān)考表”就可以查看本人所監(jiān)考的所有場(chǎng)次。班級(jí)的監(jiān)考表也是同樣原理。點(diǎn)擊“監(jiān)考安排總表”中“生成教師監(jiān)考表”按鈕，即可在“教師監(jiān)考表”中生成以教師姓名排序的監(jiān)考記錄。

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

Private Sub CommandButton1-Click()

Application.ScreenUpdating=False

Dim zbArow, jsArow As Integer

Dim rngI, rangJ As Range

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

With Sheets("教師監(jiān)考表")

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

Acol=Application.CountA(Sheets("教師監(jiān)考表").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("監(jiān)考安排總表").Range("J2:J" & zbArow)

Acol=Application.CountA(Sheets("教師監(jiān)考表").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

'-------------------對(duì)生成記錄按教師姓名進(jìn)行排序------------------------

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

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

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

End With

5 結(jié)語(yǔ)

教學(xué)管理人員使用本系統(tǒng)來(lái)編排監(jiān)考表,工作效率有了很大提高,減少了大量重復(fù)信息的輸入,節(jié)省了編排時(shí)間。自動(dòng)生成的教師監(jiān)考表和班級(jí)監(jiān)考表,更方便教師和學(xué)生查閱。