Here is an email from one of our YouTube channel subscribers on how to create a timer in Excel for examinations in educational institutions.

Hi ,
My name is Geetha. I have got one challenging task at my work.
Our department head wants to create a timed test in Excel. Once the student opens an Excel
Workbook a timer should start and run for 30 minutes. At 30 minutes the workbook should be saved
as a read only file and closed automatically.
I’ve been able to create the timer from your video at:
How to Create Timer-Counter Using Excel VBA

I’m not able to save the file as read only and am unable to start the timer when the workbook opens. I want to save the file with the roll number or ID included in the filename.

Can you help me with this, plz?

Regards,
Geetha

So Geetha wants to create a timer in Excel that will:

• Start when the user opens her Excel workbook
• The user takes his exam, let’s say, for thirty minutes. For the sake of demonstration we have run the timer for 10 seconds; the value of a cell changes by 1 every second. You can, of course, set the timer to 1800 to cover 30 minutes
• After exactly 30 minutes the timer stops
• The Excel file is saved as a read-only file
• The workbook closes
• The workbook is stored in a special folder
• The complete code can be hidden from the user by:

1. Opening the Visual Basic Editor (VBE)
2. Clicking on Tools
3. Selecting VBAProject Properties…
4. Selecting Protection tab
5. Setting your password
6. Click OK

The complete VBA code for the Timer is given below:

Sub startTimer()
Application.OnTime Now + TimeValue(“00:00:01”), _
“Increment_Count_By_1”
End Sub

Sub Increment_Count_By_1()
startTimer
Range(“A1”).Value = Range(“A1”).Value + 1
If Range(“A1”).Value = 10 Then
endTimer
myfile
End If

End Sub

Sub endTimer()
Application.OnTime Now + TimeValue(“00:00:01”), _
“Increment_Count_By_1”, Schedule:=False
End Sub

Sub myfile()
Dim Path As String
Dim name1 As String
Dim myfilename As String
Dim mydate As String

Path = “C:\ExcelExams\”
name1 = Range(“C1”).Value
mydate = Date
mydate = Format(mydate, “mm_dd_yyyy”)
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=Path & name1 & “_” & mydate & “.xlsx”, FileFormat:=51
myfilename = ActiveWorkbook.FullName
SetAttr myfilename, vbReadOnly
Application.DisplayAlerts = True
ActiveWorkbook.Close SaveChanges:=False

End Sub

Private Sub Workbook_Open()
Range(“A1”).Value = 0
Dim roll1 As String
Dim roll2 As String
roll1 = InputBox(“Please enter your roll number”)
roll2 = InputBox(“please enter your roll no again”)
If StrComp(roll1, roll2) = 0 Then
Range(“C1”).Value = roll2
Increment_Count_By_1
Else
MsgBox “Entered Roll numbers don’t match. Try again!”
End If

End Sub

Background info about the OnTime Method in MS Excel:

Syntax:

Application.OnTime(EarliestTime, Procedure, LatestTime, Schedule)

EarliestTime: required parameter. The data type is a Variant. It indicates the time when you want to run the procedure.

Procedure: required parameter. The data type is a String. The name of the procedure or macro that it should call.

LatestTime: optional parameter. The data type is a Variant. It indicates the latest time the procedure can be run.

Schedule: optional parameter. The data type it takes is a Variant. The default value of this parameter is set to TRUE. If it is set to FALSE then it clears a previously set feature.

Given below are the file extension string numbers which you can use when you are performing a SaveAs using Excel VBA:

File-Extension-String = “.xlsb”: FileFormatNumber = 50
File-Extension-String = “.xlsx”: FileFormatNumber = 51
File-Extension-String = “.xlsm”: FileFormatNumber = 52
File-Extension-String = “.csv”: FileFormatNumber = 6
File-Extension-String = “.txt”: FileFormatNumber = -4158
File-Extension-String = “.prn”: FileFormatNumber = 36

Now watch the training video (74 MB) below:

You can also view the excel training video here:
How to create a timer in Excel using VB

Further reading:
OnTime Method in Excel