But the number generated by RAND() will change
every time there is a recalculation.
Yeah, real useful, huh? :-(
Once the number is generated, is there a way to
make it NOT to change under any situation?
Copy-and-paste-special-value. You can overwrite the RAND() formulas,
or paste-special the values somewhere else and simply ignore the cells
with RAND(). An advantage of the latter is that you can "regenerate"
random values again later simply by using copy-and-paste-special-value
again.
Alteratively, create the following macro.
function myrand(optional rng as range)
myrand = Rnd
end function
The argument provides the option of causing the MYRAND formulas to be
recalculated automagically simply by modifying a cell in the range
argument. For example, =MYRAND(A1) is recalculated whenever A1 is
modified. (There are other ways to accomplish the same thing.)
If you are not familiar with macros, press alt-F11, click on
Insert -> Module, and copy-and-paste the above function into the VBE
window. Be sure to set macro security to Medium or higher in order to
make life a little easier when you reopen the workbook.
A couple downsides to the macro approach.
First, it makes it more difficult to share your workbook with other
people, since it depends on their macro security.
Second, the VBA Rnd function might not be as robust as the Excel RAND
function. I don't know that for a fact. But the VBA Rnd function
returns a 32-bit floating-point value, whereas the Excel VBA RAND
function presumably returns a 64-bit floating-point value.
On the other hand, what really determines robustness of an RNG is the
internal algorithm, which might not be reflected in the function data
type. A 64-bit result does not necessarily have a longer period or
better random characteristics. Perhaps some other people can comment,
if they have knowledge of the internal algorithms of Rnd and RAND.
