CODEWITHSUNDEEP
excelrandomvba
ali srn
ali srn

Reputation: 573

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.

Upvotes: 0

Views: 148

Answers (2)

Pierre
Pierre

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

Egan Wolf
Egan Wolf

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

Related Questions