Scoring the Answers

The ScoreAnswers() sub procedure called at the end of the game from the MathGame() procedure reads the questions asked during the game from variable arrays and displays them on the worksheet. This procedure also checks the user's answers and outputs the score as a percentage of questions answered correctly.

I use a For/Next loop to iterate through the arrays holding the questions and answers, because I know the number of questions that were asked during the game is stored in the module level variable numQuestions. The lower bound on the arrays are zero, so the looping variable ranges from zero to the number of questions less one.

String concatenation is used to output the questions asked during the game to column A on the worksheet. The user's answers are output to column B on the worksheet. Using the looping variable as the indices for the arrays guarantees that the questions match their corresponding answer.

To display the correct answer in column C of the worksheet, a formula string is created and copied to the appropriate cell using the Formula property of the cell range. Because a _ was used to display multiplication in column A, an If/Then decision structure replaces it with Excel's required multiplication operator (*) in the formula for column C. If the user entered a wrong answer, the answer is displayed in red and the integer variable numWrong is incremented by one. Finally, the user's score is calculated and output to the end of column B on the worksheet as a formula.

Private Sub ScoreAnswers()

'After the test is over, the user's answers are scored and the 'results written to the worksheet. Dim I As Integer Dim numWrong As Integer

'Loop through the arrays and score answers. Mark wrong answers in red. 'Write the questions, user answers, and correct answers to the worksheet.

Cells(I + 2, "A").Value = mathQuestions(0, I) & mathOperators(I) & mathQuestions(1, I)

If mathOperators(I) = "x" Then 'Excel requires asterisk (*) for multiplication. Cells(I + 2, "C").Formula = "=" & mathQuestions(0, I) & "*" & mathQuestions(1, I)

Cells(I + 2, "B").Font.Color = RGB(0, 0, 0)


Cells(I + 2, "C").Formula = "=" & mathQuestions(0, I) & mathOperators(I) & mathQuestions(1, I)

Cells(I + 2, "B").Font.Color = RGB(0, 0, 0) End If

If Cells(I + 2, "B").Value <> Cells(I + 2, "C").Value Then Cells(I + 2, "B").Font.Color = RGB(255, 0, 0) numWrong = numWrong + 1 End If Next I

'Compute % correct and write to the worksheet.

Cells(I + 2, "A").Value = "Score (%)" Cells(I + 2, "B").Font.Color = RGB(0, 0, 0)

Cells(I + 2, "B").Formula = "=" & (numQuestions - numWrong) / numQuestions & "*100" End Sub

Figure 4.17 shows the Math Game program worksheet immediately after a game is played.

The Math Game program.

This concludes the Math Game program. I wrote the program following the algorithm described earlier. I added small details usually related to formatting the spreadsheet to the appropriate procedures after the program was working to satisfaction.

I wrote the Math Game program using a single code module. To add a small amount of complexity to the program, you could separate the procedures listed earlier into two or more code modules. For example, some programmers prefer to leave only event procedures in object modules and locate all of their custom procedures in standard modules. Splitting the code for the Math Game program into an object and standard module is left and an exercise for the reader. As a hint, be aware of variable scope for those variables required in both modules.

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