ExcelBanter - Prevent automatic random generation

"teteperreira" wrote:
I am using the formula:
=INDEX(A356:A377,ROUND(RAND()*COUNTA(A356:A377),0) )
To generate a random name from a list. It works fine, but
everytime I do something else on the spreadsheet it generates
another random name form the list.

And every time you change some other worksheet in the same
workbook. Klunk!

I want it to generate the name once, that is all and not keep
doing it. Can someone help?

Y'mean you do not see the obvious benefit and rationale for
making RAND() a volatile function!? (Dripping in sarcasm.)

Presumably, you also do not want to use the F9 trick explained
on the RAND() help page, which replaces the cell formula with
its result -- perhaps because you might want to generate new
random results later at __your__ discretion, or at the very least
you want to see the formula that you used. (What a concept!
Drip, drip, drip ....)

Presumably you also don't think it is reasonable to simply
enable manual calculation (Tools Options Calculation) --
oh, and don't forget to disable "recalculate before save".
How unreasonable of you! (Dripping in sarcasm again.)

My solution is to create a macro, cleverly named myrand(),
which simply returns Rnd(), a VBA function. I even define an
optional parameter so that myrand() can be recomputed if
a change is made to a dependent cell or range.

Of course, that means that your worksheet now has at least
one macro -- an inconvenience that requires you and your
users choose an appropriate macro security level.

Also, the solution is not perfect. myrand() is still recalculated
whenever I delete a worksheet from the workbook -- unless
I disable auto calc before deleting the worksheet. (Office
Excel 2003.)

(Even with the latter precaution, myrand() is recalculated if
I re-enable auto calc after deleting the worksheet. Sigh.)

I probably should also point out that the VBA Rnd() function
is not exactly the same as the Excel RAND() function. At least,
not externally. VBA Rnd() returns a single-precision floating
point value, whereas I ass-u-me that Excel RAND() returns a
double-precision floating pointer value. I think you would be
hard-pressed to see the difference; but it is possible. Besides,
I have no idea where or not the internal algorithms are the
same and, if not, which is better. __That__ could make a
noticable difference in some circumstances, albeit perhaps not
yours.

HTH. I would welcome feedback from MVPs about my approach.