jeudi 5 janvier 2017

Random Number Generation in Excel

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