title: Funny Behavior of Excel VBA Random number routine
body: <p>I am trying to generate a bunch of random permutations via the the following algorithm through vba:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>What <code>RandNumber</code> essentially does is that it randomly gives a permutation based on the bottom and top values provided by the user. Example <code>RandNumber(1,2,1)</code> will either be <code>1</code> or <code>2</code> randomly.</p>
  
  <p>Now I am testing this function through the following routine</p>
  
  <pre><code>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
  </code></pre>
  
  <p>To my utter amazement the 'random' numbers are repeating exactly after 256 runs! (Ok, the value 2560 for <code>i</code> above is not exactly a co-incidence. I was suspecting that there is some pattern going on around 256 rows and thus took <code>i</code> as 2560)</p>
  
  <p>Moreover, when I test the above function with slightly modified subroutine:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>the pattern is gone. (At least the pattern of repeating after 256 values is gone. Not sure if another pattern emerges).</p>
  
  <p>Now based on my knowledge the <code>Randomize</code> is supposed to control the randomness by generating appropriate seeds and <code>Randomize</code> in vba takes the seed from the <code>timer</code>. So my hypothesis is that in the 1st <code>RandNos()</code> 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 <code>timer</code> and with it the seed of the Random numbers - supports my hypothesis.</p>
  
  <p>So my question here is </p>
  
  <ol>
  <li>Is my hypothesis correct.</li>
  <li>Should I still hope to generate a 'random' pattern in Excel VBA.</li>
  <li>Am I making a wrong use of <code>Randomize</code> here</li>
  </ol>
  
  <p>Thanks in advance for your suggestions on the issue.</p>
  
  <p>EDIT: One of the suggestions in the comment was that we should call <code>Randomize</code> only once. I tried doing that and it seems to work. However, I would still like to know what goes wrong using <code>Randomize</code> as above.</p>