UDF results in #VALUE! when invoked from spreadsheet, but OK in single-step (F8)

[danleonida]

In my application, the user needs control of the seed of a pseudo-random number generator (PRNG). I implemented the RAND() function as a UDF in VBA. It works fine in single-step (F8) but results in #VALUE! when invoked from the spreadsheet. I cannot figure out why! Here's the code:
<o></o>

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

<o></o>
Everything seems to be working fine when I single-step it (F8) but when invoked from the main spreadsheet, it returns the '#VALUE!' error. Hm!
<o></o>
Here's a 45K file that illustrates the above behaviour:
<o></o>
2012.11.30...RND.xls
<o></o>
Hope somebody knows what's 'possessing' the file! Thx!
<o></o>
danleonida-at-yahoo-dot-com

 

[Colin Legg]

This seems to be a duplicate of:
http://www.mrexcel.com/forum/excel-...value-when-invoked-but-ok-single-step-f8.html

 

[danleonida]

This seems to be a duplicate of:
http://www.mrexcel.com/forum/excel-...value-when-invoked-but-ok-single-step-f8.html

Sorry! I thought my computer crashed when I clicked 'Post', but I guess it didn't!

My apology!