Compute a static random number (compute it once) in Excel

Question

I'm looking for a way to compute a random number once in Excel. So its computed the first time its called, but then it doesn't change afterwards.

So for example, if I had something like this in B1 RANDONCE(A1), then the first time I put a value in A1 it would compute a random value but then it wouldn't change again. Or at least not until I changed A1 again.

I would like to do this without manually recopying B1 to turn it from a formula to a value as described here. Use of macros is fine.

Answer 1

I think I have a much easier way to do this. Complete your spreadsheet, then apply the =RANDBETWEEN function for that column of numbers. Then do this:

1. Copy the results from that column.
2. Highlight the column and select "paste special" and select "values".

Now you have the values most recently created and they are static.

Answer 2

You need a UDF with memory, so it knows if the cell has changed

This UDF will return a new random value when the refered to call changes, otherwise returns the last random value (ie no change)
Also return blank if source cell is blank (may or may not be what you require?)

Note: it has the problem that the Static values are lost when the sheet is closed, so the value will change each time the sheet is opened.

Function randonce(r As Range)
    Static trigger As Variant
    Static v As Double
    If r <> trigger Then
        v = Rnd
        trigger = r
    End If
    If Len(r) <> 0 Then
        randonce = v
    Else
        randonce = vbNullString
    End If
End Function