VBA random numbers produces a repeating sequence at regular intervals

Question

This code is supposed to generate a sequence of 10,000 random numbers in VBA. For some reason I am only able to produce a unique sequence of length 5842, and then it repeats. But, and this is the strangest part, each time I run the code, the sequence starts in a different place. For example in one run, the elements following element 2660 are the same as those following element 8502 (8502-2660= 5842). The next run, I get a sequence that repeats following elements 3704 and 9546 (9546-3704=5842). And so on.

Function NormRand() As Double
' NormRand returns a randomly distributed drawing from a
' standard normal distribution i.e. one with:
' Average = 0 and Standard Deviation = 1.0
Dim fac As Double, rsq As Double, v1 As Double, v2 As Double
Static flag As Boolean, gset As Double

' Each pass through the calculation of the routine produces
'  two normally-distributed deviates, so we only need to do
'  the calculations every other call. So we set the flag
'  variable (to true) if gset contains a spare NormRand value.
If flag Then
    NormRand = gset
    ' Force calculation next time.
    flag = False
Else
    ' Don't have anything saved so need to find a pair of values
    ' First generate a co-ordinate pair within the unit circle:
    Do
        v1 = 2 * Rnd - 1#
        v2 = 2 * Rnd - 1#
        rsq = v1 * v1 + v2 * v2
    Loop Until rsq <= 1#

    ' Do the Math:
    fac = Sqr(-2# * Log(rsq) / rsq)

    ' Return one of the values and save the other (gset) for next time:
    NormRand = v2 * fac
    gset = v1 * fac
    flag = True
End If

End Function

You many need to specify Randomize for one thing. https://wellsr.com/vba/2017/excel/vba-random-number-with-rnd-and-randomize/ — QHarr, Sep 04 '18 at 19:11
From that link: "You can pass Randomize an argument, but let’s not worry about that." It would have been helpful, IMHO if the author had explained why one might want to use a parameter, i.e. testing. I was not aware that the current version (VBA) uses the system timer as a default seed. I'm pretty sure in VB 6 you have to actually pass a number parameter and I would use `Randomize(Timer)` but knowing that if you pass the same seed number that you'll get the same sequence of "random" numbers helps with testing. — Bill Hileman, Sep 04 '18 at 20:10
I'm not sure how you determined that the pattern repeats at an exact spot, but if that holds true (since a computer cannot create a truly random algorithm) it might be useful to re-seed the generator after 5,000 generated numbers using Randomize another time. — Bill Hileman, Sep 04 '18 at 20:13
I plot the entire 10,000 and one can visually see where it repeats. I then confirmed it numerically. — Dean Smith, Sep 04 '18 at 20:16
As @Bill Hileman stated above, random number generators use a seed, it must be re-seeding itself, you should re-seed with a random, every 100 or 1,000 cycles. A random seeded with a random. It will open up the range of your repetition if the first rand without seed given - based on the clock, passed into the second rand as a seed, the seed will be changing. It is very interesting how computer alogrithms choose to seed a random. How many randoms do you need to generate? — Wookies-Will-Code, Sep 04 '18 at 20:57

Gustav · Accepted Answer · 2018-09-05T12:17:14.553

For some reason I am only able to produce a unique sequence of length 5842, and then it repeats. But, and this is the strangest part, each time I run the code, the sequence starts in a different place

That's by design and well known - that's why the number generation is labelled pseudo random and not random.

By the way, I notice that you are multiplying the two values. That may not be a good idea - as mentioned here.

In your function, you may try to replace Rnd with RndDbl:

Public Function RndDbl(Optional ByRef Number As Single) As Double

    ' Exponent to shift the significant digits of a single to
    ' the least significant digits of a double.
    Const Exponent  As Long = 7

    Dim Value       As Double

    ' Generate two values like:
    '   0.1851513
    '   0.000000072890967130661
    ' and add these.
    Value = CDbl(Rnd(Number)) + CDbl(Rnd(Number) * 10 ^ -Exponent)

    RndDbl = Value

End Function

and then modify your code to include a dynamic seed by calling Timer:

Do
    v1 = 2 * RndDbl(-Timer) - 1#
    v2 = 2 * RndDbl(-Timer) - 1#
    rsq = v1 + v2
Loop Until rsq <= 1#

The generated values will still not be true random, but should not take form of a repeated sequence.

VBA random numbers produces a repeating sequence at regular intervals

1 Answers1