Funny Behavior of Excel VBA Random number routine

Question

I am trying to generate a bunch of random permutations via the the following algorithm through vba:

Function RandNumber(Bottom As Integer, Top As Integer, _
                Amount As Integer) As Integer()

Dim iArr As Variant
Dim i As Integer
Dim r As Integer
Dim temp As Integer
Dim bridge() As Integer

'Application.Volatile

ReDim iArr(Bottom To Top)

For i = Bottom To Top
    iArr(i) = i
Next i
Randomize
For i = Top To Bottom + 1 Step -1
    r = Int(Rnd() * (i - Bottom + 1)) + Bottom
    temp = iArr(r)
    iArr(r) = iArr(i)
    iArr(i) = temp
Next i

ReDim Preserve bridge(1 To Amount)
For i = Bottom To Bottom + Amount - 1
    bridge(i - Bottom + 1) = iArr(i)
Next i

RandNumber = bridge

End Function

What RandNumber essentially does is that it randomly gives a permutation based on the bottom and top values provided by the user. Example RandNumber(1,2,1) will either be 1 or 2 randomly.

Now I am testing this function through the following routine

Sub RandNos()
Dim z() As Variant
ReDim Preserve z(1 To 2560)
For i = 1 To 2560
z(i) = RandNumber(1, 2, 1)
Next i
For i = 1 To 2560
ThisWorkbook.Sheets("Sheet2").Range("A1").Offset(i - 1, 0) = z(i)
Next i
End Sub

To my utter amazement the 'random' numbers are repeating exactly after 256 runs! (Ok, the value 2560 for i above is not exactly a co-incidence. I was suspecting that there is some pattern going on around 256 rows and thus took i as 2560)

Moreover, when I test the above function with slightly modified subroutine:

Sub RandNos2()
For i = 1 To 2560
ActiveSheet.Range("A1").Offset((i - 1) Mod 256 + 1, Int((i - 1) / 256)) = RandNumber(1, 2, 1)
Next i
End Sub

the pattern is gone. (At least the pattern of repeating after 256 values is gone. Not sure if another pattern emerges).

Now based on my knowledge the Randomize is supposed to control the randomness by generating appropriate seeds and Randomize in vba takes the seed from the timer. So my hypothesis is that in the 1st RandNos() subroutine the updates happen so quickly that the seeds don't get updated fast enough. The fact that pattern is gone when I test with the second subroutine, since in the second routine excel takes longer to write the code in the worksheet and hence the gives the code some chance to update the timer and with it the seed of the Random numbers - supports my hypothesis.

So my question here is

1. Is my hypothesis correct.
2. Should I still hope to generate a 'random' pattern in Excel VBA.
3. Am I making a wrong use of Randomize here

Thanks in advance for your suggestions on the issue.

EDIT: One of the suggestions in the comment was that we should call Randomize only once. I tried doing that and it seems to work. However, I would still like to know what goes wrong using Randomize as above.

Answer 1

in answer to your edit, your hypothesis is correct.

Randomize takes the time and generates a seed. A seed is just a starting point for Rnd.

However, note that Randomize is not one-to-one which means that for any given input into Randomize (i.e. the time) it doesn't generate the same seed every time. You appear to have discovered that Randomize has a sequence of 256 seeds for every given input. Therefore, you get a repeating sequence of 256 numbers which were supposed to be random but which clearly are not.

Reference: The VBA help page for Randomize and CS classes

Answer 2

You should call Randomize once only. If RandNos() is called more than once, use Randomize in the method that calls RandNos().

Answer 3

just for future reference to anyone else who encounters this problem, I decided to just try "slowing down" the subroutine enough to allow the system timer to reset. Application.wait did not work well, but I found that by including a simple debug.print line above the randomize call, it slowed the execution down just enough to get it to not repeat every 256. This not dramatically increase the overall run time of the subroutine. Just a thought for folks who would not mind sacrificing a little bit of optimization for a very simple fix on the pseudo-randomness.