Excel's RAND(): Freezing & De-Freezing?

[Michael]

Hello everyone,

Suppose I am using a modeler/optimizer (e.g. MPL or AMPL). I use a VBA
code to run that modeler/optimizer several times through a VBA loop
which calls MPL each time.

Each time the model runs, it reads a value from an excel cell which
has rand() in it and writes it after each single run. How do I use VBA
to freeze and de-freeze that cell so it does change only when needed;
NOT everytime I breeze (you know what I mean)!?

Thanks,
Mike

 

[Bob Phillips]

Mike,

Here's one way.

Goto Tools>Options and on the Calculation tab click the Iteration checkbox
(this will suppress circular reference messages)

Put this formula in A1, or whichever cell has the RAND generator

=IF(($B$1="")+(A1>0),A1,INT(100*RAND()))

This will intially show a zero. To generate the random number, put anything
in B1. To regenerate, clear B1, the edit A1, don't change it, just edit it
to reset back to 0. Then put something in B1 magain.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[A.W.J. Ales]

I don't think you can for just that cell.
What you can do (if that is acceptable for the rest of your program) is :
1) Stop automatic calculation and reinstate it just before the calculation
is needed.
2) Remove the rand() formula from the cell and place it just before a new
calculation is needed.
I suppose however that this might not be acceptable because I'm not sure
wether you get each time again the same (start-)sequence of random numbers ?
3) You could place the cell on a seperate sheet from which you set the
EnableCalculation property to False and set it back to True if a new
calculation is needed.


--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *