View Single Post
  #11  
Old March 26th, 2010, 10:10 PM posted to microsoft.public.excel.worksheet.functions
Dana DeLouis[_3_]
external usenet poster
 
Posts: 184
Default How to get repeatable Excel RAND sequence?

Hi. As a side note, this is interesting because I can't get vba's Rnd
values using your equation at Machine Precision. However, if we bump
the precision up just a little, then we can do what Rnd does. I wonder
what Excel is actually doing? Very interesting. :)


Private Sub Workbook_Open()
Dim a, b, k
Dim x
Dim R

a = 1140671485
b = 12820163
k = 2 ^ 24

'// First 50 Rnd calls
For R = 1 To 50
Cells(R, 1) = Rnd
Next R

'// Double as in Workbook
x = CDbl(327680)
For R = 1 To 50
If x 1 Then x = x * k
x = dMod(x * a + b, k) / k
Cells(R, 2) = x
Next R

'// Higher Precision for vba
x = CDec(327680)
For R = 1 To 50
If x 1 Then x = x * k
x = dMod(x * a + b, k) / k
Cells(R, 3) = x
Next R

Range("A1:C50").NumberFormat = "0.00000000000000000"
End Sub

Excel rnd VBA Rnd
0.7055475115776060 0.7055475115776060
0.5334241390228270 0.5334240198135370
0.5581560134887690 0.5795186161994930
0.5320560932159420 0.2895624637603760
0.7160687446594230 0.3019480109214780
0.0968921184539795 0.7747400999069210
0.4058379530906680 0.0140176415443420
0.3854335546493530 0.7607235908508300
0.9148474335670470 0.8144900202751150
0.3595037460327150 0.7090378999710080



snip
= = = = = = =
Dana DeLouis