RND function same result after 5 runs !

[JohnSeito]

Hello people,

The RND function in VBA puzzles me.

The story goes like this, I have a large worksheet with a large set of
information in it. For example it has from line 2 to line 16k (I know this is not
a lot but just use this number as an example), with line one as the header.

What I am trying to do is put random number for each of these records from line 2
to link 16k. I used two methods to doing this,

ONE - the first is I used a loop that goes from
2 to the last row in the list, and store from 2 to the last number of the list in the collection.
I then randomly output from this collection and put them on to the list for each record, so a second
loop to randomly put the collected number for each record.

This is my code for the first method.

lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Dim C As New Collection, I As Integer

For I = 2 To lastRow
    C.Add CStr(I), CStr(I)
Next

For J = 2 To lastRow
    I = Int(Rnd * C.Count + 1)
    Cells(J, "H") = C.Item(I)
    C.Remove (I)
Next

SECOND method - I don't use collection at all, I just use the RND function to randomly choose a number from
1 to say 1 million and what ever random number that comes out from the RND function I put it next to the list.

For J = 2 To lastrow

     ranNUMBER = Int((1000000 * Rnd) + 1)
     Cells(J, "H") = ranNUMBER

Next

The problem I notice with both these two methods is that after I run it, and I run each of these method for like 5 times.
I noticed that for each record on the list, the random number is the same all the times I have run it. This is not what I had
in mind of what it was suppose to do, I thought if I run it the first time, each item on the list would have this number, and then
I run it again it would have a different number.

So row 225, column H. I run it 5 times, all 5 times it would give me the number for example 3456.

I am not sure why, could someone please help me understand what is going on. Thanks !!

 

[AlphaFrog]

Hi AlphaFrog, Thanks for your message, I have been busy over the weekend. Hope you had a good weekend.

Was wondering who is Rothstein, and why only once before the loop, I put it in the loop and before the RND it looks like its working fine.

That link you provided has a lot of coding to it, I have to dig into it. Thanks !!!

Rick Rothstein the guy who reminded you to check the link I provided, and the guy within the link that made the recommendation. Just ask him the question. He's already subscribed to this thread and he's the RND expert. I was just passing on his recommendation. I have a basic understanding of how it works, but I certainly couldn't explain it well.

 

[Rick Rothstein]

Rick Rothstein the guy who reminded you to check the link I provided, and the guy within the link that made the recommendation. Just ask him the question. He's already subscribed to this thread and he's the RND expert. I was just passing on his recommendation. I have a basic understanding of how it works, but I certainly couldn't explain it well.

I am not sure I can explain it well either as I am not really a RND expert (I was a math major in college, but my tastes ran to the purely theoretical and random numbers belong more to the application side of mathematics, statistics being the branch, I believe). In any event, I'll give everyone my thoughts on it. VB's random number generator has some number of sets of random numbers, each set consisting of millions of numbers. When the Randomize command is issued, one of those sets is chosen and every Rnd function call takes a number, in order, from the sequence in the chosen set. However, if you issue the Randomize command before every call to the Rnd function, you only will see he first number in the chosen set... you can never see past that first sequence number because the next time you call Rnd, you reset which set Rnd will use by issuing a Randomize command. I suspect there are a lot less sets, and hence a lot less first numbers, than the millions of numbers making up any one set, let alone each of the sets. Thus, issuing a Randomize command before every call to the Rnd function limits the number of values that can be returned to you, hence, you should not do that. For those who have not run the experiment I offered in the message AlphaFrog linked to in Message #6, I would urge you to do so, it quite clearly visually demonstrates the problem and setting it up is extremely easy to do (the code does the bulk of the work for you).

 

[JohnSeito]

Hi Rothstein,

Interesting, how you delve so deep into the Rnd function.
So using Randomize is not a good solution and what would you suggest ?

However, if you issue the Randomize command before every call to the Rnd function, you only will see he first number in the chosen set...

So how big is the set of the chosen set ? I think using the randomize before the RND function is easy, but as you mentioned is not the
best solution, unfortunately, and what kind of consequence can it have ?

I suspect there are a lot less sets, and hence a lot less first numbers, than the millions of numbers making up any one set, let alone each of the sets.

so would this set be able to do the job if I have for example 20k lines of information and I want it to be fair to all of them that they each gets to be randomize and chosen, not sure if I am speaking correctly, but what I meant is would there be some items from the list of 20k that it will never be pick be out because of this chosen set and the limit of the number it can return to us, How much limit then ?

if my line turn out to be 100k how would this limit be affected ?

I was thinking instead of choosing a random number from 1 to 1 million, I just read how many line items I have and then put the number in a collection
and then output randomly (randomize before the RND). I think this solution is better than randomly choosing from 1 to 1 million since there is a limit according to you, what do you think ?

Choosing randomly is like the % an item gets selected, that is how I see it.


Again thanks for sharing and thanks for the info.