volatile v non-volatile

  • Thread starter Thread starter CJ
  • Start date Start date
C

CJ

Excel 2003. How can I switch from volatile to non-volatile? I don't want
randomly generated numbers to change every time the enter button is pushed or
a cell is modified. Thanks all.
 
Hi,

You can turn calculation to manual but this of course affects all formula.
Aside from that there's not much you can do if your using RAND to get your
random numbers.

There are ways of getting non volatile random numbers and if you describe
what you want, someone will help.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
I have a multiplication table for a child to learn with. I don't want the
randomly generated questions to change value until after the child has
completed all of the questions.
 
CJ said:
Excel 2003. How can I switch from volatile to non-volatile?
I don't want randomly generated numbers to change every
time the enter button is pushed or a cell is modified.
Click to expand...

Create the following UDF:

Function myRAND(Optional r As Range) As Double
myRAND = Evaluate("RAND()")
End Function

(There are advantages of using Evaluate("RAND()") instead of VBA Rnd.)

You can call it as =myRAND(). It will not change until the entire worksheet
is recalculated, or you cause that cell to be recalculated (e.g. press F2,
Enter).

You can call is as =myRAND(B1). It will not change until B1 is
recalculated, or the entire worksheet is recalculated, or you cause that cell
to be recalculated.

Similarly, you have the following UDF:

Function myRANDBETWEEN(lo As Double, _
hi As Double, Optional r As Range) As Double
myRANDBETWEEN = _
Evaluate("RANDBETWEEN(" & lo & "," & hi & ")")
End Function


You wrote in another message:
I don't want the randomly generated questions to
change value until after the child has completed
all of the questions.
Click to expand...

If all your questions use myRANDBETWEEN(10,99,B1), for example, you can
create a button to execute the following macro:

Sub newQuestions()
Range("b1") = ""
End Sub

To associate a button with the macro, click on View > Toolbars > Forms.
 
I'd suppose to renew all the task with a subroutine with changed fixed values :

Sub MultipTable()
Const NProb As Long = 10
Dim I As Long, J As Long, P(1 To 2) As String
For I = 1 To NProb
With Cells(I, 1)
For J = 1 To 2
.Formula = "=Randbetween(1,9)"
P(J) = CStr(.Value)
Next J
.Value = P(1) & " × " & P(2)
End With
Next I
End Sub

I hope not to spoil weekend for a lot of poor pupils.
 
Back
Top