In trying to solve this question, I wrote the following in an attempt to implement the Box-Muller transform to generate random normal variables in pure VBA:
Function RandNorm(Optional mean As Double = 0, Optional sd As Double = 1) As Double
Dim s As Double
s = Sqr(-2 * Log(Rnd())) * Cos(6.283185307 * Rnd()) '6.28 etc. is 2*pi
RandNorm = mean + sd * s
End Function
The following somewhat weak test always works, returning a number close to 0:
Sub test1()
Randomize
Dim s As Double
Dim i As Long
For i = 1 To 17000000
s = s + RandNorm()
Next i
Debug.Print s / 17000000
End Sub
On the other hand, the following test never works (because it tries to take the log of 0, which is undefined):
Sub test2()
Randomize
Dim s As Double
Dim i As Long
Debug.Print Rnd() 'just to clock it
For i = 1 To 17000000
s = s + RandNorm()
Next i
Debug.Print s / 17000000
End Sub
The problem is that rnd() returns 0 on average once out of every 2^24 (a bit less than 17,000,000) calls. It is of course easy enough to tweak the definition of RandNorm to avoid the zero (see the linked-to question), but I am still puzzled by the above code. It would make perfect sense to me if each test failed half the time (when the zero is fed into Log()) and worked half the time (when the zero is fed into Cos()). It seems that Randomize avoids at least half of the possible seeds.
Why does Randomize behave this way? Is there a way to seed the random number generator so that all possible states of the random number generator can occur?
On Edit
If I define the following sub:
Sub ReRandomize()
Dim r As Double
Randomize
If Rnd() > 0.5 Then r = Rnd()
End Sub
And modify test1 and test2 above to use ReRandomize instead of Randomize, both of the test subs will fail 50% of the time, so that might answer the part of the question about if there is "a way to seed the random number generator so that all possible states of the random number generator can occur?" It is still mysterious as to why Randomize behaves the way that it does. This is the second time that an Excel VBA question made me realize that Randomize is a weird sub. None of this matters very much for typical use of rnd(), but it does underscore that it is a somewhat low quality random number generator which shouldn't be used for serious statistical work.
