10

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.

Community
  • 1
  • 1
studgeek
  • 14,272
  • 6
  • 84
  • 96
  • 1
    How long does the value need to stay "fixed"? Just across a session, or after saving, etc? – Tim Williams Nov 04 '11 at 16:12
  • If you'd be happy with a value that isn't actually 'random', just opaque, you could use something linked from http://stackoverflow.com/questions/3498356/md5-hash-function-in-excel to compute a hash of the value in `A1`. – AakashM Nov 04 '11 at 16:25
  • Sounds like you want an event. I added an answer below. Note that you can assign B1 it's value (from a formula) in one step in VBA, so you don't need to do it like they posted in that link you added. – Gaijinhunter Nov 05 '11 at 02:14
  • [How to keep random numbers from changing in Excel](https://superuser.com/q/745747/241386) – phuclv Sep 26 '17 at 01:39

6 Answers6

5

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.

Adam
  • 51
  • 1
  • 2
4

You can also use circular references to make a purely formula-driven "toggle switch," allowing the user to calculate a set of random numbers and then turn off further recalculations. Turn off circular reference warnings, and then put this formula in cell B3:

=IF($B$1="YES",RAND(),B3)

If cell B1 contains "YES", B3 will generate a new random number with each spreadsheet recalculation; if B1 contains any other value, the current value of B3 will be retained.

Scott Forbes
  • 7,397
  • 1
  • 26
  • 39
  • This is a best solution for me. However, I didn't get any warnings. The formula worked as it is for me without any warnings. – ihightower Dec 04 '22 at 17:50
4

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
chris neilsen
  • 52,446
  • 10
  • 84
  • 123
  • 1
    Thank you for this answer, @ChrisNeilsen! It only needed a few small tweaks to be an all-in-one solution. **First line:** `Function RandOnce(Low As Long, High As Long, r As Range) As Long` **Change** `v = Rnd` **to** `v = Int(Rnd * (High + 1 - Low)) + Low` and **add 'Randomize'** after the declarations to make sure the RNG gets reset. – TesseractE Dec 27 '14 at 17:09
3

You can create a UDF (User-Defined Function):

Public Function Rand_once(ByVal r As Range)
    Rand_once = Rnd
End Function

In the cell where you want the result, you can put:

=Rand_once(A1)

The value will change (actually, being recalculated) only when the source value changes (aka A1).

JMax
  • 26,109
  • 12
  • 69
  • 88
1

This obviously won't be the best solution if you have to track many cells, but if it's just A1 you need to track for changes, you can use an event to do your function in B1, then at the end, assign it the value it was given. I find this the most simple solution and it works as you require it to.

Example:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" Then
    With Cells(1, 2)
        .Value = "=rand()" 'or whatever
        .Value = .Value
    End With
End If

End Sub
Gaijinhunter
  • 14,587
  • 4
  • 51
  • 57
  • If you have to track many cells, you can use an `Intersect` to make it more readable. Yet, I find this solution quite great as it will obviously not change the value (whenever Full Recalc or so, see the debate with Chris below my answer) until `A1` would change (so I think this desserves a +1 :)) – JMax Nov 05 '11 at 08:57
0
Declare Function GetCurrentProcessId Lib "kernel32" () As Long
Public Function MySessionRand(Optional seedMod As Single = 0) As Single
    Dim seed As Single
    seed = (-1 * GetCurrentProcessId) - seedMod
    MySessionRand = Rnd(seed)
End Function

This solution uses current PID value of Excel as a seed. Multiplication by -1 force same number every time a rnd() is called, so as long as a session is active, the PID won't change. 'seedMod' is there to help re-seed function if needed, and can be used with for eg. ROW() or COLUMN().

Public Function MySessionRand2(Optional seed As Single = 0, Optional min As Double = 0, Optional max As Long = 1) As Double
    ' if called with default values will produce either 0 or 1
    If min >= max Then max = min + 1
    MySessionRand2 = Int((max - min + 1) * MySessionRand(seed) + min)
End Function

And supplementary function for random range.

TylerH
  • 20,799
  • 66
  • 75
  • 101
Machine
  • 31
  • 4