VBA RND and Randomize Repetition / Period Problem

I have come across a rather odd issue with excel / VBA and the RND function. The issue is that repeatedly reinitializing the RND function with Randomize results in a lot of repeated "random" values; what looks like a random number generator with a very short period.

Now my understanding from these two MS links;

http://msdn.microsoft.com/en-us/libr...=vs.90%29.aspx
http://msdn.microsoft.com/en-us/libr...=vs.90%29.aspx

Is that the randomize statement reinitialize the RND function using a seed value from the system clock. Since random numbers generated after repeated randomize statements are not the same, it would seem that the seed values generated by the system timer are in some way conspiring to produce a short period of random numbers.

And surprise surprise there seems to be an issue with it;
http://support2.microsoft.com/defaul...b;en-us;120587
but I am not sure why or how this produces the results I am seeing.

Can anyone shed some light on whats going on?

See code below. If I enable the randomize statement in rand_wrapper() function (highlighted with lots of #########s) I get a lot of repeated values for the random number, if not I dont.

Please Login or Register  to view this content.

Hi nb-,

The following code describes my understanding of how random number generator seeding works. I prefer to use my own seed number, rather than rely on the Excel timer as you do.

Please Login or Register  to view this content.

Lewis

Lewis, thanks for the info. I think I have some dubious typing in my code. I also note you are properly reinitializing RND before setting the seed.

I am however still at a loss as to what the cause of the repetition I am getting is? E.g. in my example above for 10,000 numbers I essentially get 2,000 'random' numbers each repeated 5 times.

Hi nb-,

Thanks for the reply.

It seems the native VB RND function only has a period of 16,777,216 which is ridiculous.

That is a given, since the Excel random number generator uses Single Precision (32 bit) Math. Back in the day, I rarely used the random number generators supplied by the compilers I used, but used a custom algorithm (long since lost by me), which had been known to give excellent pseudo-random results.

I am however still at a loss as to what the cause of the repetition I am getting is? E.g. in my example above for 10,000 numbers I essentially get 2,000 'random' numbers each repeated 5 times.

I ran your code exactly as written, with the addition of a comparison loop after the 'Quick Sort'. Each time I ran the code, I got 10,000 unique numbers. I also 'eyeball tested' goodness of fit by counting the numbers in certain ranges

Please Login or Register  to view this content.

and putting the results in rows 1 to 100. Without any statistical tests, the numbers seemed evenly distributed as I would expect.

----------------

Please Login or Register  to view this content.

In the above code, I think the Randomize statement is unnecessary, as you are reseeding the values during each random number access.

Lewis

The issue I get is actually much worse here than 5x repetition its more like 50x repetition in 10,000.

Indeed the randomize statement in the rand_wrapper() function is unnecessary. Without the randomize statement I get perfectly sensible looking numbers but with it I get a great deal of repetition (excel 2010 / win 8.1) I am surprised you aren't able to replicate this problem I get it every time I run the code.
E.g (lowest 199 values sorted from 10,000)

With Randomize ;

Please Login or Register  to view this content.

Without Randomize;

Please Login or Register  to view this content.

All my numbers are different with and without randomize. However I am running Excel 2003 on a Vista 32 bit system (circa 2008). Who says older isn't better.

Lewis

Hum... I wonder what it is, i get the same issues in excel 2003. I wonder what can be causing it... have to try it on a different machine.

Could be the system clock generating the same seed each time with Randomize. Try using your own seed number. My post #2 in this thread has an example.