RAND function recalculating

[cgphung]

I use the RAND function to generate numbers for math problems in a worksheet. The user is asked to enter an answer. However, when the answer is enetered by the user, the worksheete refreshes itself and regenerates new numbers which always makes the answer enetered is wrong. Is there anyway to make the RAND function do not regenerate new number unless it is activated by some other action.

Please help!

CGP

[rlv]

RAND() is a volitile function. Someone here probably knows a more elegant way, but this seems to work.

Function NVRand() As Double
NVRand = VBA.Rnd
End Function

You'll have to come up with a way to tickle it when you want to generate a new (pseudo) random number.

[cgphung]

Thank you for the Function! Now I need to figure out how to tickle it to generate a new random number. Does anyone has an idea?

CGPHUNG:think:

[mikerickson]

Function NVRand(inVal as Boolean) As Double
If inVal Then
NVRand = VBA.Rnd
Else
NVRand = Application.Caller.Value
End If
End Function

Put =NVRand($A$1) in your range of cells. Enter TRUE in A1. There are the Random Values. Enter False in A1. The values are frozen, until you True again

[cgphung]

Thank you for all the sugestions! I can insert a button and assign the macro 'Refresh' to do the trick.


Sub Refresh()
Range("D7") = MyRnd()
End Sub

Public Function MyRnd() As Double
MyRnd = Rnd
End Function

CGP
:thumb