The Rand function is XL is volatile which means that it recals every time a
calcualtion occures. Here is a UDF that is not volatile so it will evealuate
when it is first entered but not with each calculation.

Public Function StaticRand(Optional ByVal rng As Range) As Single
StaticRand = Rnd()
End Function

To force it to recalc you can point it at a cell and then just change the
value of that cell. Add the code to a standard code module. Use it in a sheet
something like this...

=staticrand()
The above is truely static and will only change if you edit the cell the
formula is in.

=staticrand(A1)
The above is static until you change the value of A1 at which point the
formula will re-evaluate.
--
HTH...

Jim Thomlinson


"Jo" wrote:

Hi everyone,

Say you have in A1, A2 two formulas with Rand() in each. Say you have
a macro that does freez A1 when it hits its target value, while A2
keeps running till it hits its target value.

My question is how to freez Rand()?

Thanks,
Jo