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

[danleonida]

I have an application that requires the user to be in control of the seed of a pseudo random number generator (PRNG). To this end, I duplicated the RAND() function in a UDF. It runs OK in single-step, but NOT when invoked from the spreadsheet. I cannot figure out why. Here's the UDF:

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

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 UDF! Thx!
danleonida-at-yahoo-dot-com

 

[Colin Legg]

Hi,

When called from a worksheet, a UDF can only return a value to the cell(s) which called it. It cannot change the values in other cells. This means that your code here:

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

is illegal and will cause the function to error out.

Some other things I noticed -

It'd be best to give your function a different name. Its name conflicts with VBA's own Rnd() function which can be used to return a random number.
All range values which are being read inside your function should be passed in as arguments. This will ensure that the calculation dependency tree doesn't get messed up.
VBA has a Randomize statement which lets the user set the seed. Have a look at the 'Randomize Statement' topic in the VBA helpfile.

Hope that helps...

 

[danleonida]

Hope that helps...

'Help' would be the undestatement of the year!

Randomize is what I wanted/needed all along only I had no idea it existed, nobody mentioned it in the context of PRNGs so I never googled it!

Thx again!

D.Leonida