What I thought would be quick excel vba, doesn't seem to be quick anymore. Basically, i want to loop through a set of questions within a given time. when the given time has expired or all questions completed, exit the loop (close the form and return). BTW, would like to show a timer countdown on the form as well. is it possible to achieve it?
I can get a form to show questions with answer options but not sure how to add the time criteria.
As far I know, this is not an ease task to achieve. Briefly speaking, one of the possible ways to do it is:
1. write your own class module for this program
2. create a form with questions, through the form constructor (Form_Initialize) create a new instance of your class (from point 1.) and link it to the form through variables, in addition your form needs to have variables to pass the information supplied by user at a later stage (your questions) to the object behind for storing
3. when your class is instantiated, the object will have a piece of code that contains a timer, and this timer will fire events, let's say, every second to refresh your form (to show how much time is left for answering questions), see UserForm.Repaint method. The questions that have been answered so far would be re-inserted into the form on repaint (info should be stored in your class object, then re-used to update the form through timer events).
Check this link as a starting point:
https://msdn.microsoft.com/en-us/library/office/gg264327.aspx
or search for: RaiseEvent Statement in Excel Help (in VB Editor).
To see how to create vba classes:
http://www.cimaware.com/resources/article_39.html
In general, you need to search for information on how to:
create class modules,
separate object model (instantiated class) from the form,
pass information between the form and the model,
make the model to repaint your form at regular intervals (firing events)
I think Application.OnTime method may can help.
Scheduling Events With OnTime And Windows Timers
---updated---
As requested in comment section, I include the essential part on how the function works
'variable to keep time interval the timer run again
Public RunWhen As Double
'main function that kick off the exam
Public Sub startExam()
'a label display how much time left, says start with 60 seconds
DisplayTime.Caption = "60"
'call a function that load and display question on screen
'assume call LoadQuestion again when user provided quiz answer, not implemented
LoadQuestion (1)
'start the
call Timer()
End Sub
'the timer function repeat itself
Sub Timer()
'set the next run time for Timer function
RunWhen = Now + TimeSerial(0, 0, 1)
'set the schedule
Application.OnTime EarliestTime:=RunWhen, Procedure:="Timer", Schedule:=True
'update the time left on screen
UserForm1.DisplayTime.Caption = UserForm1.DisplayTime.Caption - 1
'if the time deduced to 0, stop the schedule and alert user
If UserForm1.DisplayTime.Caption = "0" Then
Application.OnTime EarliestTime:=RunWhen, Procedure:="Timer", chedule:=False
MsgBox "TimesUp"
End If
End Sub
