Locking RAND()?

[Jo]

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

 

[Chip Pearson]

You would have to replace the =RAND() formula with its value when you want
to "freeze" it. E.g.,

Range("A1").Value = Range("A1").Value

Setting the Value property wipes out the formula so the cell will no longer
be updated (ever).


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Bernie Deitrick]

Jo,

Perhaps something along these lines - put the formula

=RAND()*ROW()*13

into both A1 and A2, and run the macro....

Sub FreezeRandValues()
Dim TargetValue1 As Double
Dim TargetValue2 As Double

'Formulas in A1 and A2 are
' =RAND()*ROW()*13

TargetValue1 = 12.89
TargetValue2 = 25.89

While Range("A1").Value < TargetValue1
Application.Calculate
If Range("A2").Value > TargetValue2 Then
Range("A2").Value = Range("A2").Value
End If
Wend

Range("A1").Value = Range("A1").Value

While Range("A2").Value < TargetValue2
Application.Calculate
Wend

Range("A2").Value = Range("A2").Value

End Sub

 

[Guest]

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.