I am currently working on an Excel file which stores words in English and their meanings in Turkish. What I do is, Excel randomly choose a word and show me, I click Ok and then it shows me the meaning of the word. This works fine. Problem is, there are 587 words in Excel but I feel like it keeps asking the same words.
Do you think it is my misunderstanding or Excel cannot generate very good random numbers? What I can do more to make it better?
Here is my code.
Sub askRandom()
Dim maxRow As Integer
Dim randNum As Integer
Dim answer As Integer
Dim known As Integer
Dim unknown As Integer
Dim sht As Worksheet
Dim resultSheet As Worksheet
Set sht = ThisWorkbook.Sheets("Words")
Set resultSheet = ThisWorkbook.Sheets("Results")
maxRow = sht.Range("A10000").End(3).Row
Do While answer <> vbCancel
randNum = Int(maxRow * Rnd() + 1)
MsgBox sht.Cells(randNum, 1).Value, , "Word is"
answer = MsgBox(sht.Cells(randNum, 2).Value & ", " & sht.Cells(randNum, 3).Value & _
", " & sht.Cells(randNum, 4).Value & sht.Cells(randNum, 5).Value, vbYesNoCancel, _
"Meanings are")
If answer = vbYes Then
known = known + 1
If sht.Cells(randNum, 6).Value <> 0 Then
sht.Cells(randNum, 6).Value = sht.Cells(randNum, 6).Value - 1
End If
ElseIf answer = vbNo Then
unknown = unknown + 1
sht.Cells(randNum, 6).Value = sht.Cells(randNum, 6).Value + 1
End If
Loop
MsgBox "Your results are " & known & " out of " & unknown + known
With resultSheet
.Cells(.Range("A10000").End(3).Row + 1, 1).Value = known
.Cells(.Range("B10000").End(3).Row + 1, 2).Value = unknown
.Cells(.Range("C10000").End(3).Row + 1, 3).Value = Format((known / (known + unknown)) * 100, "%##.0")
.Cells(.Range("D10000").End(3).Row + 1, 4).Value = Now
End With
End Sub
I thought, I can't make random number generation work better but maybe I can shuffle the words' order. Can I do that? I'm open to innovative solutions.
Aucun commentaire:
Enregistrer un commentaire