can you have randbetween not RE-calculate?

J

jwomack

I am making facotring practice for my students and want to use the
randbetween but do not want it to recalculate everytime.
 
D

David Billigmeier

Couple options:

1) You can turn automatic calculation off in your workbook so every time you
want the rand function to re-calculate you have to hit the F9 key...
Tools->Options->'Calculation' Tab->Check 'Manual'

2) You can set up a UDF, something like:
Function rand2()
rand2 = Rnd
End Function

3) When you enter the randbetween() function instead of commiting it by
pressing "Enter" commit by pressing F9
 
H

Harald Staff

Formulas recalculate. Try selecting the cells and run a little random number
writing macro instead, something like:

Sub RandNums()
Dim L As Long
Dim H As Long
Dim Cel As Range

L = 10 'min
H = 50 'max

Randomize
For Each Cel In Selection
Cel.Value = L + Int(Rnd * (H - L + 1))
Next
End Sub

HTH. Best wishes Harald
 
B

Bernd Plumhoff

Hello,

instead of using
=randbetween(LOW,HIGH)
you could use
=uniqrandint(HIGH-LOW+1,FALSE)+LOW-1
entered normally (no array-formula).
See www.sulprobil.com for my UDF uniqrandint().

The second parameter (FALSE) tells my function to be non-volatile, that
means: do not recalculate with each F9.

HTH,
Bernd
 
Top