Starting the Timer

The essence of the Math Game program is contained within the sub procedure appropriately named MathGame(). This procedure controls the game's clock and calls the sub procedures that score the user's answers when the clock reaches zero. The clock is controlled with a very special method of the Application object—the OnTime() method. You can use the OnTime() method to set up repetitive calls to the same procedure based on a given time increment; in this case, one second.

To begin, the MathGame() procedure uses the integer variable numSeconds to hold the amount of time left in the game. The length of the game is held in the constant TIMEALLOWED. The number of seconds left in the game is calculated by the VBA function DateDiff() using the current time and the time the program was initiated with the click of the Command Button control cmdBegin (stored in the module level variable curDate). This value is written to cell I3 on the worksheet with a defined name of Clock.

In order to count down in one second intervals, a date one second later than the current time is calculated by adding the two dates returned from VBA's Now() and TimeValue() functions. This date is then assigned to the variable nextTime. The Now() function returns the current date and time and the TimeValue() function returns a date converted from a string (formatted using hours:minutes:seconds). I passed the TimeValue() function a string specifying one second ("00:00:01"). As you are about to see, the nextTime variable is used to specify the next time the MathGame() procedure executes.

The most interesting statement in the MathGame() procedure comes next. The OnTime() method that belongs to the Application object is set up to repeatedly call the MathGame() sub procedure. The OnTime() method takes up to four parameters for input, two of which are required. Because I only need to pass the OnTime() method three parameters, I am using named arguments. The EarliestTime parameter represents the next time the system will call the procedure specified by the Procedure parameter, in this case the MathGame() procedure. The EarliestTime and Procedure parameters are required. The other two parameters, both of which are optional are LatestTime and Schedule. The LatestTime parameter represents the latest time the procedure specified by the Procedure parameter can be called; however it is not required here. The Schedule parameter is used to schedule a new call to the procedure specified by the Procedure parameter. In this case, Schedule must be used and set to true in order to ensure the next call to the MathGame() procedure occurs. It is important to point out that between calls to the MathGame() procedure, the system is allowed to process other events; thus, the system is not locked up processing code as it would be if we used a looping structure to handle the timer. This allows the user to enter answers into the appropriate worksheet cell. The MathGame() procedure is now set up to execute every second. Figure 4.16 shows the Math Game program worksheet during a game. The timer started at 60 seconds.

Following the initial use of the OnTime() method, an If/Then decision structure is used to check the value of the timer. If the timer is less than or equal to zero, then the OnTime() method is used to disable the timer by setting the Schedule parameter to false; thus, the MathGame() procedure will no longer be called. Without this statement, the MathGame() procedure will be called every second and drastic action (ctrl+alt+break) will have to be taken to stop the program.

The Math Game worksheet as the program is running.

After the timer reaches zero, calls to the procedures EnableControls(), ClearBoard(), and ScoreAnswers() are made to enable the ActiveX controls, clear the values in the spreadsheet cells containing the question and answer, and score the results of the game.

Private Sub MathGame()

'Manages the clock while testing. Calls scoring procedures when test is over. Dim numSeconds As Integer Dim nextTime As Date Const TIMEALLOWED = 60

numSeconds = DateDiff("s", curDate, Now)

'Start the clock.

Range("Clock").Value = TIMEALLOWED - numSeconds nextTime = Now + TimeValue("00:00:01")

Application.OnTime EarliestTime:=nextTime, Procedure:="MathGameSheet.MathGame", Schedule:=True

Timer Excel

'Disable timer when it reaches zero, score results, and clean up 'worksheet controls/cells.

If (TIMEALLOWED - numSeconds <= 0) Then gameRunning = False

Application.OnTime EarliestTime:=nextTime, Procedure:="MathGameSheet.MathGame", Schedule:=True

EnableControls True

ClearBoard

ScoreAnswers

Application.MoveAfterReturn = True End If End Sub

The MathGame() procedure handles the timer and scoring when the game is over, but it does not collect the questions or user's answers. Instead, these values are captured in the Change() event of the worksheet.

Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook


Post a comment