Reputation: 573
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.
Upvotes: 0
Views: 148
Reputation: 1046
As Egan Wolf said, you need to use randomize.
The generator of VBA is not of high statistical quality, but does the trick most of the time. You may code Low discrepancy sequences and seed them with randomize >> rnd to have better quality random variables.
Upvotes: 1
Reputation: 3573
You can try to use Randomize
statement before calling Rnd()
. It gives new seed for generator and may help in getting different numbers.
Upvotes: 2