Now you don't need to search here and there for conducting online exam for your students. If your students are in Standard VII or below it then you can use this beautiful technique to conduct exam for them. For students above standard VII you can read my another post which is about 3 Best free websites to conduct online exam.
Why you will do this with Google docs
- Google docs is a free service which will allow you to create web form using Text box, Dropdown, Radio button, Check box and many others.
- You can create multiple choice questions with Google docs using Radio buttons.
- Students below standard VII are small children and you might not want to create an exam which will take a lot of time to design. Google docs will take very less time to design.
- When students will complete the exam all their answers will be populated in an excel sheet and you can easily evaluate their answers using Visual Basic program.
How to do this
- Lets start doing this assuming that you know how to create web forms with Google docs.
- If for the first time you are listening about Google docs or you are new to Google docs then also don't need to worry. Just sign in to your Google account and go to www.docs.google.com, you will automatically learn everything.
- Create a form with Name(Text field), Roll number(Text field) and the questions(Radio buttons).
- You can view a sample exam which I have created for you.
How to evaluate the answers
- After the students appeared the exam, open Google docs and you can find their responses in a sheet.
- Now a very important step is, after all students appear the exam, you have to also appear the exam with all correct answers so that your response will also be recorded in the same Google sheet. Make sure that your entry must be the last entry in the sheet. In the above picture you can see the last entry with my name. That means I deliberately appeared the exam giving all the correct answers so that my answers will be compared with others to evaluate their answers.
- Then Go to File-->Download as-->Microsoft Excel. Now the answers will be downloaded as Excel.
- In Excel you can see only one sheet. The name of the sheet must be Sheet1. If it is not so, then change it to Sheet1.
- Now press alt+f11 to open VB editor. Then in the left side-bar double click on Sheet1 (Sheet1) to open the editor. Then copy and paste the following VB code.
Sub evaluateExam()
On Error GoTo err
isErrorOccured = False
If (isErrorOccured) Then
err: MsgBox "An error occured while evaluating the answers. You may not get the correct result. This happened because you have given some invalid data. Please run the program again.", vbOKOnly, "Error"
Exit Sub
End If
WorksheetExists = False
For Each Sh In ActiveWorkbook.Worksheets
If Sh.Name = "AllResult" Then WorksheetExists = True
Next Sh
If (WorksheetExists) Then
Application.DisplayAlerts = False
Sheets("AllResult").Delete
Application.DisplayAlerts = True
End If
WorksheetExists = False
For Each Sh In ActiveWorkbook.Worksheets
If Sh.Name = "FinalResult" Then WorksheetExists = True
Next Sh
If (WorksheetExists) Then
Application.DisplayAlerts = False
Sheets("FinalResult").Delete
Application.DisplayAlerts = True
End If
quesRange = Split(InputBox("The questions are present from which column to which column ?", "Question Range"), "-")
startCol = Range(Trim(quesRange(0)) & 1).Column
endCol = Range(Trim(quesRange(1)) & 1).Column
Dim WS As Worksheet
Set WS = Sheets.Add(After:=Sheets(Worksheets.Count))
WS.Name = "AllResult"
correctRow = Sheets("Sheet1").UsedRange.Rows.Count
For i = 1 To startCol - 1
Sheets("AllResult").Range(Split(Cells(, i).Address, "$")(1) + Trim(Str(1))).Value = Sheets(1).Range(Split(Cells(, i).Address, "$")(1) + Trim(Str(1))).Value
Sheets("AllResult").Range(Split(Cells(, i).Address, "$")(1) + Trim(Str(1))).Font.Name = "Cambria"
Sheets("AllResult").Range(Split(Cells(, i).Address, "$")(1) + Trim(Str(1))).Font.Bold = True
Sheets("AllResult").Range(Split(Cells(, i).Address, "$")(1) + Trim(Str(1))).Font.Size = 14
Sheets("AllResult").Range(Split(Cells(, i).Address, "$")(1) + Trim(Str(1))).HorizontalAlignment = xlCenter
Next
For i = startCol To endCol
Sheets("AllResult").Range(Split(Cells(, i).Address, "$")(1) + Trim(Str(1))).Value = "Q" + Trim(Str(i - startCol + 1))
Sheets("AllResult").Range(Split(Cells(, i).Address, "$")(1) + Trim(Str(1))).Font.Name = "Cambria"
Sheets("AllResult").Range(Split(Cells(, i).Address, "$")(1) + Trim(Str(1))).Font.Bold = True
Sheets("AllResult").Range(Split(Cells(, i).Address, "$")(1) + Trim(Str(1))).Font.Size = 14
Sheets("AllResult").Range(Split(Cells(, i).Address, "$")(1) + Trim(Str(1))).HorizontalAlignment = xlCenter
Next
For j = 2 To Sheets("Sheet1").UsedRange.Rows.Count - 1
For k = 1 To startCol - 1
Sheets("AllResult").Range(Split(Cells(, k).Address, "$")(1) + Trim(Str(j))).Value = Sheets(1).Range(Split(Cells(, k).Address, "$")(1) + Trim(Str(j))).Value
Sheets("AllResult").Range(Split(Cells(, k).Address, "$")(1) + Trim(Str(j))).Font.Name = "Cambria"
Sheets("AllResult").Range(Split(Cells(, k).Address, "$")(1) + Trim(Str(j))).Font.Size = 12
Next
For i = startCol To endCol
If (Sheets("Sheet1").Range(Split(Cells(, i).Address, "$")(1) + Trim(Str(j))).Value = Sheets("Sheet1").Range(Split(Cells(, i).Address, "$")(1) + Trim(Str(correctRow))).Value) Then
Sheets("AllResult").Range(Split(Cells(, i).Address, "$")(1) + Trim(Str(j))).Value = "C"
Sheets("AllResult").Range(Split(Cells(, i).Address, "$")(1) + Trim(Str(j))).Font.Name = "Cambria"
Sheets("AllResult").Range(Split(Cells(, i).Address, "$")(1) + Trim(Str(j))).Font.Size = 12
Sheets("AllResult").Range(Split(Cells(, i).Address, "$")(1) + Trim(Str(j))).HorizontalAlignment = xlCenter
Sheets("AllResult").Range(Split(Cells(, i).Address, "$")(1) + Trim(Str(j))).Font.Color = &HC000&
Else
Sheets("AllResult").Range(Split(Cells(, i).Address, "$")(1) + Trim(Str(j))).Value = "X"
Sheets("AllResult").Range(Split(Cells(, i).Address, "$")(1) + Trim(Str(j))).Font.Name = "Cambria"
Sheets("AllResult").Range(Split(Cells(, i).Address, "$")(1) + Trim(Str(j))).Font.Size = 12
Sheets("AllResult").Range(Split(Cells(, i).Address, "$")(1) + Trim(Str(j))).HorizontalAlignment = xlCenter
Sheets("AllResult").Range(Split(Cells(, i).Address, "$")(1) + Trim(Str(j))).Font.Color = vbRed
End If
Next
Next
ActiveSheet.UsedRange.EntireColumn.AutoFit
'Code for FinalResult sheet starts here
eachMark = InputBox("Each correct answer carries how much mark ?", "Mark per question")
negativeMark = InputBox("Each wrong answer deducts how much mark ?", "Negative marking")
passPercentage = InputBox("Minimum percentage required to pass the exam ?", "Passing percentage")
Set WS = Sheets.Add(After:=Sheets(Worksheets.Count))
WS.Name = "FinalResult"
For i = 1 To startCol - 1
Sheets("FinalResult").Range(Split(Cells(, i).Address, "$")(1) + Trim(Str(1))).Value = Sheets(1).Range(Split(Cells(, i).Address, "$")(1) + Trim(Str(1))).Value
Sheets("FinalResult").Range(Split(Cells(, i).Address, "$")(1) + Trim(Str(1))).Font.Name = "Cambria"
Sheets("FinalResult").Range(Split(Cells(, i).Address, "$")(1) + Trim(Str(1))).Font.Bold = True
Sheets("FinalResult").Range(Split(Cells(, i).Address, "$")(1) + Trim(Str(1))).Font.Size = 14
Sheets("FinalResult").Range(Split(Cells(, i).Address, "$")(1) + Trim(Str(1))).HorizontalAlignment = xlCenter
Next
Sheets("FinalResult").Range(Split(Cells(, startCol).Address, "$")(1) + Trim(Str(1))).Value = "Correct"
Sheets("FinalResult").Range(Split(Cells(, startCol).Address, "$")(1) + Trim(Str(1))).Font.Name = "Cambria"
Sheets("FinalResult").Range(Split(Cells(, startCol).Address, "$")(1) + Trim(Str(1))).Font.Bold = True
Sheets("FinalResult").Range(Split(Cells(, startCol).Address, "$")(1) + Trim(Str(1))).Font.Size = 14
Sheets("FinalResult").Range(Split(Cells(, startCol).Address, "$")(1) + Trim(Str(1))).HorizontalAlignment = xlCenter
Sheets("FinalResult").Range(Split(Cells(, startCol + 1).Address, "$")(1) + Trim(Str(1))).Value = "Wrong"
Sheets("FinalResult").Range(Split(Cells(, startCol + 1).Address, "$")(1) + Trim(Str(1))).Font.Name = "Cambria"
Sheets("FinalResult").Range(Split(Cells(, startCol + 1).Address, "$")(1) + Trim(Str(1))).Font.Bold = True
Sheets("FinalResult").Range(Split(Cells(, startCol + 1).Address, "$")(1) + Trim(Str(1))).Font.Size = 14
Sheets("FinalResult").Range(Split(Cells(, startCol + 1).Address, "$")(1) + Trim(Str(1))).HorizontalAlignment = xlCenter
Sheets("FinalResult").Range(Split(Cells(, startCol + 2).Address, "$")(1) + Trim(Str(1))).Value = "Score"
Sheets("FinalResult").Range(Split(Cells(, startCol + 2).Address, "$")(1) + Trim(Str(1))).Font.Name = "Cambria"
Sheets("FinalResult").Range(Split(Cells(, startCol + 2).Address, "$")(1) + Trim(Str(1))).Font.Bold = True
Sheets("FinalResult").Range(Split(Cells(, startCol + 2).Address, "$")(1) + Trim(Str(1))).Font.Size = 14
Sheets("FinalResult").Range(Split(Cells(, startCol + 2).Address, "$")(1) + Trim(Str(1))).HorizontalAlignment = xlCenter
Sheets("FinalResult").Range(Split(Cells(, startCol + 3).Address, "$")(1) + Trim(Str(1))).Value = "Percentage"
Sheets("FinalResult").Range(Split(Cells(, startCol + 3).Address, "$")(1) + Trim(Str(1))).Font.Name = "Cambria"
Sheets("FinalResult").Range(Split(Cells(, startCol + 3).Address, "$")(1) + Trim(Str(1))).Font.Bold = True
Sheets("FinalResult").Range(Split(Cells(, startCol + 3).Address, "$")(1) + Trim(Str(1))).Font.Size = 14
Sheets("FinalResult").Range(Split(Cells(, startCol + 3).Address, "$")(1) + Trim(Str(1))).HorizontalAlignment = xlCenter
Sheets("FinalResult").Range(Split(Cells(, startCol + 4).Address, "$")(1) + Trim(Str(1))).Value = "Status"
Sheets("FinalResult").Range(Split(Cells(, startCol + 4).Address, "$")(1) + Trim(Str(1))).Font.Name = "Cambria"
Sheets("FinalResult").Range(Split(Cells(, startCol + 4).Address, "$")(1) + Trim(Str(1))).Font.Bold = True
Sheets("FinalResult").Range(Split(Cells(, startCol + 4).Address, "$")(1) + Trim(Str(1))).Font.Size = 14
Sheets("FinalResult").Range(Split(Cells(, startCol + 4).Address, "$")(1) + Trim(Str(1))).HorizontalAlignment = xlCenter
For j = 2 To Sheets("Sheet1").UsedRange.Rows.Count - 1
For k = 1 To startCol - 1
Sheets("FinalResult").Range(Split(Cells(, k).Address, "$")(1) + Trim(Str(j))).Value = Sheets(1).Range(Split(Cells(, k).Address, "$")(1) + Trim(Str(j))).Value
Sheets("FinalResult").Range(Split(Cells(, k).Address, "$")(1) + Trim(Str(j))).Font.Name = "Cambria"
Sheets("FinalResult").Range(Split(Cells(, k).Address, "$")(1) + Trim(Str(j))).Font.Size = 12
Next
totalCorrect = 0
totalWrong = 0
For i = startCol To endCol
If (Sheets("AllResult").Range(Split(Cells(, i).Address, "$")(1) + Trim(Str(j))).Value = "C") Then
totalCorrect = totalCorrect + 1
Else
totalWrong = totalWrong + 1
End If
Next
Sheets("FinalResult").Range(Split(Cells(, startCol).Address, "$")(1) + Trim(Str(j))).Value = totalCorrect
Sheets("FinalResult").Range(Split(Cells(, startCol).Address, "$")(1) + Trim(Str(j))).Font.Name = "Cambria"
Sheets("FinalResult").Range(Split(Cells(, startCol).Address, "$")(1) + Trim(Str(j))).Font.Size = 12
Sheets("FinalResult").Range(Split(Cells(, startCol + 1).Address, "$")(1) + Trim(Str(j))).Value = totalWrong
Sheets("FinalResult").Range(Split(Cells(, startCol + 1).Address, "$")(1) + Trim(Str(j))).Font.Name = "Cambria"
Sheets("FinalResult").Range(Split(Cells(, startCol + 1).Address, "$")(1) + Trim(Str(j))).Font.Size = 12
Sheets("FinalResult").Range(Split(Cells(, startCol + 2).Address, "$")(1) + Trim(Str(j))).Value = (totalCorrect) * eachMark - totalWrong * negativeMark
Sheets("FinalResult").Range(Split(Cells(, startCol + 2).Address, "$")(1) + Trim(Str(j))).Font.Name = "Cambria"
Sheets("FinalResult").Range(Split(Cells(, startCol + 2).Address, "$")(1) + Trim(Str(j))).Font.Size = 12
Sheets("FinalResult").Range(Split(Cells(, startCol + 2).Address, "$")(1) + Trim(Str(j))).Font.Color = vbBlue
If ((((totalCorrect) * eachMark - totalWrong * negativeMark) / ((endCol - startCol + 1) * eachMark)) * 100 < 0) Then
Sheets("FinalResult").Range(Split(Cells(, startCol + 3).Address, "$")(1) + Trim(Str(j))).Value = "0%"
Else
Sheets("FinalResult").Range(Split(Cells(, startCol + 3).Address, "$")(1) + Trim(Str(j))).Value = Trim(Str((((totalCorrect) * eachMark - totalWrong * negativeMark) / ((endCol - startCol + 1) * eachMark)) * 100)) + " %"
End If
Sheets("FinalResult").Range(Split(Cells(, startCol + 3).Address, "$")(1) + Trim(Str(j))).Font.Name = "Cambria"
Sheets("FinalResult").Range(Split(Cells(, startCol + 3).Address, "$")(1) + Trim(Str(j))).Font.Size = 12
If ((((totalCorrect) * eachMark - totalWrong * negativeMark) / ((endCol - startCol + 1) * eachMark)) * 100 >= Int(passPercentage)) Then
Sheets("FinalResult").Range(Split(Cells(, startCol + 4).Address, "$")(1) + Trim(Str(j))).Value = "Pass"
Sheets("FinalResult").Range(Split(Cells(, startCol + 4).Address, "$")(1) + Trim(Str(j))).Font.Name = "Cambria"
Sheets("FinalResult").Range(Split(Cells(, startCol + 4).Address, "$")(1) + Trim(Str(j))).Font.Color = &HC000&
Sheets("FinalResult").Range(Split(Cells(, startCol + 4).Address, "$")(1) + Trim(Str(j))).Font.Size = 12
Sheets("FinalResult").Range(Split(Cells(, startCol + 4).Address, "$")(1) + Trim(Str(j))).Font.Bold = True
Sheets("FinalResult").Range(Split(Cells(, startCol + 4).Address, "$")(1) + Trim(Str(j))).HorizontalAlignment = xlCenter
Else
Sheets("FinalResult").Range(Split(Cells(, startCol + 4).Address, "$")(1) + Trim(Str(j))).Value = "Fail"
Sheets("FinalResult").Range(Split(Cells(, startCol + 4).Address, "$")(1) + Trim(Str(j))).Font.Name = "Cambria"
Sheets("FinalResult").Range(Split(Cells(, startCol + 4).Address, "$")(1) + Trim(Str(j))).Font.Color = vbRed
Sheets("FinalResult").Range(Split(Cells(, startCol + 4).Address, "$")(1) + Trim(Str(j))).Font.Size = 12
Sheets("FinalResult").Range(Split(Cells(, startCol + 4).Address, "$")(1) + Trim(Str(j))).Font.Bold = True
Sheets("FinalResult").Range(Split(Cells(, startCol + 4).Address, "$")(1) + Trim(Str(j))).HorizontalAlignment = xlCenter
End If
Next
ActiveSheet.UsedRange.EntireColumn.AutoFit
End Sub
- Then close visual basic editor and press alt+f8 to run the VB code.
- It will ask you "The questions are present from which column to which column ?"
- Please provide the range carefully. If your questions are present from D to H then provide D-H.
- Then it will ask you some more questions about. Read the questions carefully and provide only +ve integer values for all.
- The result will be automatically calculated.
