RND not truly random?

[Reset]

Out of boredom, I set out to try and make a random Excel maze generator. To start I had it plot the progression on the spreadsheet by step count just to sanity check my process. If it draws itself into a dead end the process restarts. What i have found is that it will try twice (boxing itself in) and then never get any farther. But what is most interesting is every time I run the macro it is the same two patterns generated. If I fiddle with the code a bit it will draw something different, but again will repeat the same two patterns. This leads me to think the RND function is not truly random. Does anyone have insight on it, and/or what can be done to truly randomize? I have included the code, which I know needs some cleaning up and has one previous RND line I was using.

Sub drawmaze()
linebeg: 'start
Range(Cells(1, 1), Cells(30, 14)).ClearContents
w = 0
Dim p(31, 15) As String
For s = 1 To 30
    p(s, 15) = "X"
    p(s, 0) = "X"
Next s
For t = 1 To 15
    p(31, t) = "X"
    p(0, t) = "X"
Next t
p(1, 1) = "X"
rw = 1
cl = 1
Cells(rw, cl) = "X"
line2: 'start
u = 0: d = 0: l = 0: r = 0
If rw = 1 Then u = 1: l = 1
If rw = 30 Then d = 1: l = 1
If cl = 1 Then l = 1: u = 1
If cl = 14 Then r = 1: u = 1
If p(rw - 1, cl) = "X" Then u = 1
If p(rw + 1, cl) = "X" Then d = 1: MsgBox "D"
If p(rw, cl - 1) = "X" Then l = 1
If p(rw, cl + 1) = "X" Then r = 1
If u = 1 And d = 1 And l = 1 And r = 1 Then GoTo linebeg
line1: 'find allowed move
q = 0
'Z = Round(Rnd() * 4, 0)
Z = Int((3 - 0 + 1) * Rnd + 0)
If Z = 0 And u = 0 Then p(rw - 1, cl) = "X": q = 1: rw = rw - 1
If Z = 1 And d = 0 Then p(rw + 1, cl) = "X": q = 1: rw = rw + 1
If Z = 2 And l = 0 Then p(rw, cl - 1) = "X": q = 1: cl = cl - 1
If Z = 3 And r = 0 Then p(rw, cl + 1) = "X": q = 1: cl = cl + 1
If q = 0 Then GoTo line1
w = w + 1
Cells(rw, cl) = w
If rw = 30 And cl = 14 Then GoTo lineend
GoTo line2
lineend: 'completed

End Sub

 

[AlphaFrog]

Add the Randomize command to reseed the Rnd function.

[B]Randomize[/B]
Z = Int((3 - 0 + 1) * Rnd + 0)

Randomize Function (Visual Basic)

 

[mirabeau]

... also (from shg http://www.mrexcel.com/forum/excel-questions/571521-how-generate-5-59-lotto-numbers-1-cell-4.html, Post#34)

"... random number generator ... for the <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> Rnd function, is pretty bad; it has a repeating cycle of about 16M (2^24 - 1) numbers."

 

[Rick Rothstein]

Add the Randomize command to reseed the Rnd function.

[B]Randomize[/B]
Z = Int((3 - 0 + 1) * Rnd + 0)

I would like to suggest you implement the Randomize statement this way...

'  In the area where you Dim your variabes or at the 
'  beginning of your macro if you don't Dim your variables
'
Static AlreadyRandomized As Boolean
If Not AlreadyRandomized Then
  AlreadyRandomized = True
  Randomize
End If
....
....  {{Rest of your code goes here}}
....

The reason for the above suggestion is that the Randomize statement should only be run once per session... to do otherwise actually limits the number of random values the Rnd function can return. Here is a Excel UserForm translation done by Peter T for a compiled VB program I've posted in the past that shows the problem graphically. To use it, simply add a UserForm to the VB project and copy/paste all of the code that follows into the UserForm's code window, then run the project and then click the UserForm. The two loops are identical except that the first loop (corresponding to the left-hand display) runs the Randomize statement one time before executing the loop whereas the second loop (corresponding to the right-hand display) executes the Randomize statement with each iteration of the loop. All the loops are doing is picking a random color value and displaying a pixel of that color (arranged to fill out a rectangular area). Notice how random the color distribution is in the left-hand display and how regular looking "patterns" seem to be displayed in the right-hand display. Randomize should only be executed once per running of any code that uses the Rnd function call.

Private Declare Function FindWindow Lib "user32" _
                Alias "FindWindowA" _
               (ByVal lpClassName As String, _
                ByVal lpWindowName As String) As Long

Private Declare Function SetPixel Lib "gdi32" _
               (ByVal hDc As Long, _
                ByVal x As Long, _
                ByVal y As Long, _
                ByVal crColor As Long) As Long

Private Declare Function GetPixel Lib "gdi32" _
               (ByVal hDc As Long, _
                ByVal x As Long, _
                ByVal y As Long) As Long

Private Declare Function GetDC Lib "user32" ( _
                ByVal hWnd As Long) As Long

Private Declare Function ReleaseDC Lib "user32" ( _
                ByVal hWnd As Long, _
                ByVal hDc As Long) As Long

Private Sub PaintPixels()
  Dim hWnd As Long, hDc As Long
  Dim tp As Long, lt As Long
  Dim x As Long, y As Long
  Dim colr As Long
  
  Const Z As Long = 128 * 2 - 1
  Const C As Long = 16711680
  
  Me.Left = 10: Me.Top = 10
  Me.Width = (Z * 2) * 0.75 + 45: Me.Height = Z * 0.75 + 60
  ' if form is too small, change 0.75 to 1 or 1.25
  
  hWnd = FindWindow("ThunderDFrame", Me.Caption)
  hDc = GetDC(hWnd)
  
  tp = Me.Top + 15
  lt = Me.Left + 10
  
  Randomize
  For y = tp To tp + Z
    For x = lt To lt + Z
      colr = Rnd * C
      SetPixel hDc, x, y, colr
    Next
  Next
  
  lt = lt + Z + 15
  For y = tp To tp + Z
    For x = lt To lt + Z
      Randomize
      colr = Rnd * C
      SetPixel hDc, x, y, colr
    Next
  Next
  
  ReleaseDC hWnd, hDc
End Sub
  
Private Sub UserForm_Activate()
  Me.Caption = "Click me to (re-) PaintPixels"
  PaintPixels
End Sub


Private Sub UserForm_Click()
  ' Me.Repaint
  PaintPixels
End Sub

 

[Reset]

Rick,

Interesting demonstration. I can now confirm tht it is truly random. Thanks, and to all that responded!

 

[Rick Rothstein]

Rick,

Interesting demonstration. I can now confirm tht it is truly random. Thanks, and to all that responded!

I think the reason for the difference in the "graphs" is that when you call Randomize once per loop, you only get numbers chosen from the first value in each possible sequence for a given seed (Randomize reseeds the Rnd function) and I believe the total number of these first values is far, far less than the total number of values in any one sequence when that sequence is allowed to fully run (which happens if you only use Randomize once). As for the numbers being "truly random"... you might want to re-read Message #3.