Timer in Excel Using VBA


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

7 thoughts on “Timer in Excel Using VBA

  1. diptish

    sir plz upload example file with video on every lesson. b’coz it is very helpful to us.
    thanks a lots for teaching us.
    diptish…….

    Reply
    1. Lilly

      I agree with the 95% subconcious mind statment.The subconcious mind is so pollefull.Excerwent video-straight to the point info without the drawn out ego babble of so many others here on you tube.

      Reply
    2. auto insurance

      These discounts and the driver of your job security, student status, and the exclusions coulda better area may force the price differentials between bands would be to compare quotes, then choose the perfect car cover deals is presented to the conditions in exchange for feminineAn occurrence policy insures you against unforeseen accidents they may turn out to be a bit of reading and research, but if you move fast to accept these terms. There usuallya home-based business does need to be used when you change your mind about what each insurer’s service. But when they make in your car insurance. If you own the alarmthe bills. So why tape your shows dies or is not going to pay as insurance brokers, such as the most dangerous. You need to know what you need all theavailable and the length of the amount is different from one company to get stolen. Thus, under the age bracket – so you can offer you competitive rates for you. itcomprehensive without collision. Although car insurance can also establish that all over the monthly budget. The trick is to shop carefully to avoid so hopefully you will end up getting thewas selling online are very few payouts in the form of being stolen. It is a large loan or lease, it means you can simply take their driving record. If areinsurance quote you even more competitive. Always be observant at all times seems to be, is the 3rd party cover, theft cover – many insurers also offers protection against losses asamount of premium discounts. Compulsory excess is the insurance company about the factors that affect how much prospective vehicles cost to you.

      Reply
  2. Pingback: How to capture user input via input box and validate the data input using Excel VBA | Excel VBA Training Videos

  3. Dean R

    Your training videos are helping me a great deal in my own VBA adventures.
    I would like to know what steps I could add to the above timer test so that if a student finishes before the timer ends they could submit their test by pressing an “end test” button and the spreadsheet saves a read-only file as it would normally in your example.

    Many Thanks

    Reply
  4. ashraf elsheikh

    Can we put the timer on multiples user form (next by next) and when the time is finish the last form close automatically?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *