Re: Squence Random Number Generator
I would like to draw attention to how I handled the Randomize function call and suggest that it should be used whenever random numbers are used in VBA code.
I concur. I "always" do that myself. I succumbed to a lapse of judgment this one time.
And for the same reason, my "first time" variable (e.g. AlreadyRun) is a public global variable, not a local static variable. Thus, if Randomize is called from multiple procedures, it will be called only once (until VBA is reset).
That said, and by no means trying to argue the point, I believe the risk of calling Randomize is minimized when it is called one time from a subroutine that is executed manually v. from a procedure that is executed automatically (like my UDF) or in a loop.
The reason is: Randomize with no parameter relies on the "system timer" to generate the seed. Since the "system timer" is updated at least every 15.625 msec
, it appears that calling Randomize less frequently results in Rnd sequences that are not obviously distinguishable (i.e. less "random") than when Randomize is called only once.
This is demonstrated by test1 and test3 below. test1 calls Randomize before each of 2000 Rnd calls within a 15.625 msec interval. test3 calls Randomize before each of 200 Rnd calls every 15.625 msec.
The results from test1 is very interesting. The sequence of Randomize/Rnd results restarts every 256 calls. That seems to depend on the number of calls within a 15.625 msec interval, not the elapsed time. On one of my computers, the 256 calls are completed in about 1 msec. On another computer, they are completed in about 0.1 msec.
In contrast, the results from test3 seem to be indistinguishable from test2, which calls Randomize one time, then calls Rnd 2000 times. Of course, that is not a rigorous determination. but the distribution of differences between sequential Rnd calls are similar for test2 and test3.
Rich (BB code):
Const n As Long = 2000
Sub test1()
' call Randomize before each of n Rnd calls
' within one 15.625 msec interval
Const myname As String = "test1"
Dim t As Single, i As Long
Dim r(1 To n, 1 To 1) As Double
' new results worksheet
Sheets.Add before:=Sheets(1)
On Error Resume Next
ActiveSheet.Name = myname
If Err > 0 Then
Application.DisplayAlerts = False
Sheets(myname).Delete
Application.DisplayAlerts = True
ActiveSheet.Name = myname
End If
On Error GoTo 0
' sync with system clock
t = Timer
Do: Loop Until t <> Timer
t = Timer
Do
i = i + 1
Randomize
r(i, 1) = Rnd
If i = n Then Exit Do
Loop Until t <> Timer
Range("a1:a" & i) = r
MsgBox i & " " & myname
End Sub
Sub test2()
' call Randomize one time before
' n Rnd calls within one 15.625 msec interval
Const myname As String = "test2"
Dim t As Single, i As Long
Dim r(1 To n, 1 To 1) As Double
' new results worksheet
Sheets.Add before:=Sheets(1)
On Error Resume Next
ActiveSheet.Name = myname
If Err > 0 Then
Application.DisplayAlerts = False
Sheets(myname).Delete
Application.DisplayAlerts = True
ActiveSheet.Name = myname
End If
On Error GoTo 0
Randomize
' sync with system clock
t = Timer
Do: Loop Until t <> Timer
t = Timer
Do
i = i + 1
r(i, 1) = Rnd
If i = n Then Exit Do
Loop Until t <> Timer
Range("a1:a" & i) = r
MsgBox i & " " & myname
End Sub
Sub test3()
' call Randomize/Rnd every 15.625 msec for n times
Const myname As String = "test3"
Dim t As Single, i As Long
Dim r(1 To n, 1 To 1) As Double
' new results worksheet
Sheets.Add before:=Sheets(1)
On Error Resume Next
ActiveSheet.Name = myname
If Err > 0 Then
Application.DisplayAlerts = False
Sheets(myname).Delete
Application.DisplayAlerts = True
ActiveSheet.Name = myname
End If
On Error GoTo 0
' sync with system clock
t = Timer
Do: Loop Until t <> Timer
For i = 1 To n
t = Timer
Do: Loop Until t <> Timer
Randomize
r(i, 1) = Rnd
Next
Range("a1:a" & n) = r
MsgBox n & " " & myname
End Sub
