Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 2012.11.30...RND.xls Hope someone can help! Thx in advance! danleonida-at-yahoo-dot-com |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I set up an area chart in Excel with stepped changes? | Charts and Charting in Excel | |||
Graph with stepped increases or decreases | Charts and Charting in Excel | |||
Works when stepped-through, but errors when macro ran | Excel Programming | |||
Stepped Functions | Charts and Charting in Excel | |||
Stepped fee calculation | Excel Discussion (Misc queries) |