UDF OK when single-stepped (F8) but NOT in spreadsheet
I have an application that requires the user to have control of the seed of a pseudo random number generator (PRNG). I found the MS' version of it and implemented it as a VBA UDF. It works fine when I single-step it (F8) but when invoked from the spreadsheet, it returns '#VALUE!' Here's the UDF:
Code:
Function RND() As Variant
' Implements Excel's RAND() algorithm with full control of the seed.
'
' http://support.microsoft.com/kb/828795
'
' C IX, IY, IZ SHOULD BE SET TO INTEGER VALUES BETWEEN 1 AND 30000 BEFORE FIRST ENTRY
' IX = MOD(171 * IX, 30269)
' IY = MOD(172 * IY, 30307)
' IZ = MOD(170 * IZ, 30323)
' RANDOM = AMOD(FLOAT(IX) / 30269.0 + FLOAT(IY) / 30307.0 + FLOAT(IZ) / 30323.0, 1.0)
'
Dim Xi As Long
Dim Yi As Long
Dim Zi As Long
Dim LAST_X As Long
Dim LAST_Y As Long
Dim LAST_Z As Long
LAST_X = Range("last_x").Value
LAST_Y = Range("last_y").Value
LAST_Z = Range("last_z").Value
If LAST_X = 0 Or LAST_Y = 0 Or LAST_Z = 0 Then
LAST_X = Range("seed_x").Value
LAST_Y = Range("seed_y").Value
LAST_Z = Range("seed_z").Value
End If
Xi = (171 * LAST_X) Mod 30269
Yi = (172 * LAST_Y) Mod 30307
Zi = (170 * LAST_Z) Mod 30323
Range("last_x").Value = Xi ' F8 goes back to top when function dimmed as Variant
Range("last_y").Value = Yi
Range("last_z").Value = Zi
RND = (Xi / 30269 + Yi / 30307 + Zi / 30323) - Int(Xi / 30269 + Yi / 30307 + Zi / 30323)
End Function
...and here's a 45k file that illustrates the behavior described above:
2012.11.30...RND.xls
Hope someone can help! Thx in advance!
danleonida-at-yahoo-dot-com |
