# VBA Random Integers

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”

1. Ross says:

Why not just truncate a text string and pass it back to an int? – am I missing something ;-)

2. Jayson says:

Why not use the RandBetween worksheet function?

3. fzz says:

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. Andy Holaday says:

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. Declan Lavelle says:

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. Jon Peltier says:

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. Jayson says:

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. JoshG says:

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

9. Jayson says:

@JoshG

Thanks for the explanation. Good stuff to know.

10. Doug Jenkins says:

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)?

11. Tushar Mehta says:

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}

12. Busybee says:

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

13. Busybee says:

@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 :-(