Is Excel VBA's Rnd() really this bad?

Question

I need a pseudo random number generator for 2D Monte Carlo simulation that doesn't have the characteristic hyperplanes that you get with simple LCGs. I tested the random number generator Rnd() in Excel 2013 using the following code (takes about 5 secs to run):

Sub ZoomRNG()

Randomize
For i = 1 To 1000
    Found = False
    Do
        x = Rnd()   ' 2 random numbers between 0.0 and 1.0
        y = Rnd()
        If ((x > 0.5) And (x < 0.51)) Then
            If ((y > 0.5) And (y < 0.51)) Then
                ' Write if both x & y in a narrow range
                Cells(i, 1) = i
                Cells(i, 2) = x
                Cells(i, 3) = y
                Found = True
            End If
        End If
    Loop While (Not Found)
Next i

End Sub

Here is a simple plot of x vs y from running the above code

Not only is it not very random-looking, it has more obvious hyperplanes than the infamous RANDU algorithm does in 2D. Basically, am I using the function incorrectly or is the Rnd() function in VBA actually not the least bit usable?

For comparison, here's what I get for the Mersenne Twister MT19937 in C++.

The `Randomize` function takes an argument e.g. `Randomize Now()` but actually doesn't seem to make a difference to your plot — Robin Mackenzie, Aug 11 '16 at 08:54

score 13 · Accepted Answer · edited Apr 21 '20 at 11:29

To yield a better random generator and to make its performance faster, I modified your code like this:

Const N = 1000           'Put this on top of your code module
Sub ZoomRNG()

Dim RandXY(1 To N, 1 To 3) As Single, i As Single, x As Single, y As Single

For i = 1 To N
    Randomize            'Put this in the loop to generate a better random numbers
    Do
        x = Rnd
        y = Rnd
        If x > 0.5 And x < 0.51 Then
            If y > 0.5 And y < 0.51 Then
                RandXY(i, 1) = i
                RandXY(i, 2) = x
                RandXY(i, 3) = y
                Exit Do
            End If
        End If
    Loop
Next
Cells(1, 9).Resize(N, 3) = RandXY
End Sub

I obtain this after plotting the result

The result looks better than your code's output. Modifying the above code a little bit to something like this

Const N = 1000
Sub ZoomRNG()

Dim RandXY(1 To N, 1 To 3) As Single, i As Single, x As Single, y As Single

For i = 1 To N
    Randomize
    Do
        x = Rnd
        If x > 0.5 And x < 0.51 Then
            y = Rnd
            If y > 0.5 And y < 0.51 Then
                RandXY(i, 1) = i
                RandXY(i, 2) = x
                RandXY(i, 3) = y
                Exit Do
            End If
        End If
    Loop
Next
Cells(1, 9).Resize(N, 3) = RandXY
End Sub

yields a better result than the previous one

Sure the Mersenne Twister MT19937 in C++ is still better, but the last result is quite good for conducting Monte-Carlo simulations. FWIW, you might be interested in reading this paper: On the accuracy of statistical procedures in Microsoft Excel 2010.

Thanks @Anastasiya -- the reference makes things clear. The generator in the excel worksheet function RAND() and in VBA, Rnd() are different, and the VBA one is clearly inferior. I thought that MS had improved the RNG in excel but perhaps only the RAND() one. Unfortunate because for MonteCarlo simulation it can be convenient to use a generator in VBA. — XYZ, Aug 11 '16 at 11:58
@XYZ I suspect they're both similar as there's no `Application.WorksheetFunction.Rand()` in VBA — Anastasiya-Romanova 秀, Aug 11 '16 at 12:04
@XYZ I've just found if you put a dummy variable `z = Rnd` between `x = Rnd` and `y = Rnd` to my first code will make the result much better. — Anastasiya-Romanova 秀, Aug 11 '16 at 12:15
I tested the code and although it does look a bit better, when I sorted the values, of the 1000 points, only 388 were unique (around 60% of them are duplicates). — XYZ, Aug 11 '16 at 22:44

Slai · Answer 2 · 2016-08-12T16:13:56.930

That seems like it would take on average 1000 * 100 * 100 iterations to complete and VBA is usually a bit slower than native Excel formulas. Consider this example

Sub ZoomRNG()
    t = Timer
    [a1:a1000] = "=ROW()"
    [b1:c1000] = "=RAND()/100+0.5"
    [a1:c1000] = [A1:C1000].Value
    Debug.Print CDbl(Timer - t) ' 0.0546875 seconds
End Sub

Update

It's not that bad at all! This will work too even without Randomize

Sub ZoomRNGs() ' VBA.Rnd returns Single
    t = Timer
    For i = 1 To 1000
        Cells(i, 1) = i
        Cells(i, 2) = Rnd / 100 + 0.5
        Cells(i, 3) = Rnd / 100 + 0.5
    Next i
    Debug.Print Timer - t ' 0.25 seconds
End Sub

Sub ZoomRNGd() ' the Excel Function RAND() returns Double
    t = Timer
    For i = 1 To 1000
        Cells(i, 1) = i
        Cells(i, 2) = [RAND()] / 100 + 0.5
        Cells(i, 3) = [RAND()] / 100 + 0.5
    Next i
    Debug.Print Timer - t ' 0.625 seconds
End Sub

and Single has about half of the precision of Double :

s = Rnd: d = [RAND()]
Debug.Print s; d; Len(Str(s)); Len(Str(d)) ' " 0.2895625  0.580839555868045  9  17 "

Update 2

I found C alternative that is as fast as VBA Rnd.
C:\Windows\System32\msvcrt.dll is the Microsoft C Runtime Library:

Declare Function rand Lib "msvcrt" () As Long ' this in a VBA module

and then you can use it like this x = rand / 32767 in your code:

Sub ZoomRNG()
    t = Timer
    Dim i%, x#, y#, Found As Boolean
    For i = 1 To 1000
        Found = False
        Do
            x = rand / 32767 ' RAND_MAX = 32,767
            y = rand / 32767
            If ((x > 0.5) And (x < 0.51)) Then
                If ((y > 0.5) And (y < 0.51)) Then
                    ' Write if both x & y in a narrow range
                    Cells(i, 1) = i
                    Cells(i, 2) = x
                    Cells(i, 3) = y
                    Found = True
                End If
            End If
        Loop While (Not Found)
    Next i
    Debug.Print Timer - t ' 2.875 seconds
End Sub

Thanks @Slai. Using 1000 x 100 x 100 iterations was intentional though - I want to see whether the output still looks random when looking at a small section of the overall output. However, when I use your suggestion of calling [RAND()], I get what looks like high-quality random numbers but it is much slower to get all 1000 values. — XYZ, Aug 12 '16 at 00:55
My conclusion is [RAND()] = high quality perhaps Mersenne Twister output, but painfully slow. Rnd = embarrassingly bad, should come with a warning label. — XYZ, Aug 12 '16 at 00:59
@XYZ it's a bit more complicated than that. The Excel function `RAND` seems faster than the VBA `Rnd`, but evaluating it `[]` is the slow part. `[RAND()]` is the VBA shortcut for `Evaluate("RAND()")`. I also tried `x = WorksheetFunction.RandBetween(0, 1234567890) / 1234567890` which is a bit faster but the results were as bad as `Rnd`. My advice is to do most of the calculations with Excel formulas like in my first example, and use VBA just for the things that can't be done with Excel. — Slai, Aug 12 '16 at 13:21

score 2 · Answer 3 · answered Apr 09 '18 at 10:04

After reading this question I got curious and found the paper "Assessing Excel VBA Suitability for Monte Carlo Simulation" by Alexei Botchkarev that is available here. Both RAND and RND functions are not recommended, but as pointed out in the paper the Mersenne Twister has been implemented in VBA by Jerry Wang.

A quick search led me to this nicely commented Version that has been updated the last 2015/2/28: http://www.math.sci.hiroshima-u.ac.jp/~m-mat/MT/VERSIONS/BASIC/MTwister.xlsb

Source: http://www.math.sci.hiroshima-u.ac.jp/~m-mat/MT/VERSIONS/BASIC/basic.html

score 1 · Answer 4 · answered May 09 '20 at 22:50

All LCGs will generate hyperplanes. The quality of the LCG increases with decreasing distance between these hyperplanes. So, having more hyperplanes than RANDU is a good thing.

The MT plot looks much better because it is NOT an LCG. Indeed, any non-LCG pRNG could have a random looking plot and still be a bad.

To avoid the problem of 2D correlations, you could use the same LCG for x and y but have different seeds for x and y. Of course, this will not work with RND because you cannot have two separate streams. You will need an LCG pRNG that takes the seed as an argument by reference.

score 0 · Answer 5 · answered Jul 04 '17 at 02:26

As a balance between speed and goodness, I was thinking of combining them like

for...
  z = [rand()] ' good but slow.
  for .. ' just a few
     t = z + rnd()
     t = t - int(t)
     ...

Remember that good entropy + bad entropy = better entropy.

That said, only 0.05ms per [rand()].

Is Excel VBA's Rnd() really this bad?

5 Answers5

Linked