VBA Random Integers

Posted on by Dick Kusleika

Every time I post code that includes picking a random integer, Tushar reminds me that my methods produce biased results. The last time, I swore to myself I would do it right. Here’s how the old me would have done it:

Sub RandTest1()
   
    Dim lRand As Long
    Dim i As Long
    Dim aResult() As Long
    Dim dLow As Double
    Dim dHigh As Double
   
    dLow = 1
    dHigh = 4
   
    ReDim aResult(dLow To dHigh) As Long
   
    For i = 1 To 10000
        lRand = Round(Rnd * (dHigh – dLow) + dLow, 0)
        aResult(lRand) = aResult(lRand) + 1
    Next i
   
    For i = 1 To 4
        Debug.Print i & “: “ & aResult(i)
    Next i
   
End Sub

Because I’m using Round(), it will only pick the edges ~50% as often as the middle numbers. It seems that if I simply increase my range, that is reduce dLow by 0.5 and increase dHigh by .49, my distribution will be right. Technically it won’t be right, but it will be close enough for government work. Whereas the old method wasn’t even close enough for that.

Sub RandTest2()
   
    Dim lRand As Long
    Dim i As Long
    Dim aResult() As Long
    Dim dLow As Double
    Dim dHigh As Double
   
    dLow = 1
    dHigh = 4
   
    ReDim aResult(dLow To dHigh) As Long
   
    dLow = dLow – 0.5
    dHigh = dHigh + 0.49
   
    For i = 1 To 10000
        lRand = Round(Rnd * (dHigh – dLow) + dLow, 0)
        aResult(lRand) = aResult(lRand) + 1
    Next i
   
    For i = 1 To 4
        Debug.Print i & “: “ & aResult(i)
    Next i
   
End Sub

Here’s the results from running both

Looks like a winner to me. Am I missing anything?

Posted in Uncategorized

15 thoughts on “VBA Random Integers”

  3. This is why numeric programming isn’t trivial, no matter how easy it may seem.

    Don’t round, truncate.

    Function f(lo As Long, hi As Long) As Long
      f = lo + Int(Rnd * (hi – lo + 1))
    End Function

    Putting this differently, is

    R = Round(Rnd * (H – L) + L, 0)

    really clearer than

    R = L + Int(Rnd * (H – L + 1))

    ?

    I suppose the ‘+ 1’ may not be immediately obvious. It arises from there being only N – 1 intervals bounded by N distinct boundary points. This N – 1 to N relationship between intervals and boundary points is fundamental to numeric programming.

  4. Sub RandTest2() is still biased. The upper bound will lack favor by about 1% in long runs, owing to the lack of precision in adding 0.49. Other evils creep in too, such as lRand = 0. Adjusting the precision of the offset seems futile. Not sure if that’s close enough for the guv?

    fzz’s is the way I’ve done it since way back, Apple //e days.

  5. You are indeed missing something!

    With RandTest2 you will get
    a) value 4 with probability (0.99/3.99)=24.812%; and
    b) values 1..3 with probability (each)25.0627%

    so not an accuate uniform distribution

    Best to use ‘int’ rather than ’round’. Try this instead:

    Sub RandTest3()

    Dim lRand As Long
    Dim i As Long
    Dim aResult() As Long
    Dim dLow As Double
    Dim dHigh As Double

    dLow = 1
    dHigh = 4

    ReDim aResult(dLow – 1 To dHigh) As Long

    dLow = dLow
    dHigh = dHigh

    For i = 1 To 10000
    lRand = Int(Rnd * (dHigh – dLow + 1) + dLow)
    aResult(lRand) = aResult(lRand) + 1
    Next i

    For i = 1 To 4
    Debug.Print i & “: ” & aResult(i)
    Next i

    End Sub

  6. Dick –

    fzz beat me to it, and Ross hinted at it first.

    Your second procedure is essentially calculating Low + Int(High – Low + 0.99)

    For an accountant, isn’t that somewhat precise? I guess if you’re an accountant for Enron, it’s orders of magnitude too precise.

  7. So, I’m really curious now. Why go through all the trouble of creating a random function when one exists? Is the built in function RANDBETWEEN biased? Are there advantages to building your own?

  8. Speed is one big advantage to writing your own. A few quick tests on my machine showed that my random function (like fzz’s) is about 30-50 times faster than using the RandBetween Worksheetfunction. Also, previous to 2007 (at least in 2003), you needed to have the Analysis Tool Pak installed for RandBetween. Writing your own function helps improve compatibility.

    -Josh

  10. According to “Excel for Scientists and Engineers” (E. Joseph Billo) a new algorithm for random numbers was introduced in Excel 2003, because the old one wasn’t very good. Does anyone know if VBA uses the new algorithm or the old one (or something different)?

  12. Dick wrote: “Because I’m using Round(), it will only pick the edges ~50% as often as the middle numbers. It seems that if I simply increase my range, that is reduce dLow by 0.5 and increase dHigh by .49, my distribution will be right. Technically it won’t be right, but it will be close enough for government work. Whereas the old method wasn’t even close enough for that.”

    So, why not just use INT in Excel or Int in VBA and get the correct result in the first place — subject, of course, to the limitations in RAND (or Rnd). It’s a lot easier than trying to fudge ROUND/Round. {grin}

  13. Hey,

    Does this give unique random or there are repeats ??

    I am looking @ creating a Quizz which pick unique random questions from different sections,however this below picks up repeats tooo

    For j = 1 To SA
    RD = WorksheetFunction.RandBetween(2, ACount)
    Worksheets(“Quiz Paper”).Cells(j + 10, 1) = Worksheets(“Section A”).Cells(RD, 1)
    Worksheets(“Quiz Paper”).Cells(j + 10, 2) = Worksheets(“Section A”).Cells(RD, 2)
    Worksheets(“Quiz Paper”).Cells(j + 10, 3) = “Section A”
    Worksheets(“Quiz Paper”).Cells(j + 10, 4) = Worksheets(“Section A”).Cells(RD, 3)
    Next j

  15. @Tushar

    I did go thru the page.. am just confused newbie…will it work as a worksheet function ?

    I am just not sure which is one is right for me :-(

    Thx in advance.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.