Collecting Answers

The Change() event of a worksheet triggers when the content of cells on the worksheet are changed by the user. In the Math Game program, this event will trigger every time the user enters an answer. Excel passes the altered cell's range to the Change() event via the Target parameter. The user's answers are entered into the merged range L8:M9 defined with the name Answer; therefore, the value of the Target parameter will be $L$8.

If the user has entered the answer in the correct cell, a series of statements are executed. A modicum of input validation is included in the conditional for the If/Then decision structure. If the user presses Enter without typing in an answer, then no code inside the If/Then decision structure is executed. This forces the user to enter an answer for each question. Furthermore, the gameRunning variable must be true or the code in the decision structure will not execute. (This prevents the program from displaying a question when the game is over.)

If the user does answer a question, then the numQuestions variable is incremented by one, the StoreQuestions() sub procedure is called, and a new question is obtained from calls to the GetRandomOperator() (if required) and GetOperands() procedures and displayed.

Private Sub Worksheet_Change(ByVal Target As Range) 'Stores answer entered by the user and gets next question.

If (Target.Address = "$L$8") And (Range("Answer").Value <> "") And gameRunning Then numQuestions = numQuestions + 1 StoreQuestions If optAny.Value = True Then

GetRandomOperator End If GetOperands

Range("Answer").Select Selection.Value = "" End If End Sub

The StoreQuestions() sub procedure is called from the Change() event of the worksheet, so the code within is executed every time the user enters an answer to a question. The dynamic variable arrays declared at module level are re-dimensioned to increase their size by one with each call to this procedure. The Preserve keyword is used to ensure that previously stored values are not lost.

The two-dimensional array mathQuestions maintains the same number of dimensions, and only the upper bound of the last dimension changes, as required when using the Preserve keyword. Thus, the mathQuestions array can be thought of as containing two rows (indexed by 0 and 1) and n columns where n is equal to the number of questions asked during the game.

The operands (cells F8 and I8 defined as LeftOperand and RightOperand, respectively) for each question are stored in rows 0 and 1 of the mathQuestions array. The mathematical operator used and the user's answers are stored in the arrays mathOperators and userAnswers, respectively. The index value in the arrays used to store the mathematical operators and the user's answers is identical to the index value in the array used to store the corresponding question. This is critical for outputting these values to the correct worksheet cells later in the program.

The user's answer is passed to the Val() function before storing in the array. This serves as more input validation. If the user enters a non-numerical string, then the answer will usually be set to zero depending on the string, as discussed earlier in this chapter.

Private Sub StoreQuestions()

'Stores the questions and answers in dynamic arrays.

ReDim Preserve mathQuestions(1, numQuestions) As Integer ReDim Preserve mathOperators(numQuestions) As String ReDim Preserve userAnswers(numQuestions) As Integer mathQuestions(0, numQuestions - 1) = Range("LeftOperand").Value mathQuestions(1, numQuestions - 1) = Range("RightOperand").Value mathOperators(numQuestions - 1) = Range("Operator").Value userAnswers(numQuestions - 1) = Val(Range("Answer").Value) End Sub

After the timer has reached zero, the game is over and the last question is cleared with the ClearBoard() sub procedure before the user's results are scored and tabulated.

Private Sub ClearBoard()

'Clears the operands and the answer from the worksheet cells. Range("LeftOperand").Value = "" Range("RightOperand").Value = "" Range("Answer").Value = "" End Sub

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