I know that in Excel one can use rand()
function to generate random numbers, and each time pressing F9, all rand()
results on that sheet will be refreshed.
However, now I need to repeatedly generate random numbers using VBA, and control it so that the random numbers won't refresh unless the code orders so.
For example I have
- range A1~A100, where I'd like to generate random numbers;
- column B is the calculation place where some calculation by Excel "Solver" function is executed by VBA;
- column C is where I would like to record 1,000 times calculation, each time from column B;
- the final result is an average value of column C.
Now my problems are
- I don't know how to refresh cells in column A, using VBA, How can I do this?
- I notice that, during the VBA solver function, as cells in column B are updated, each time updating, the column A random numbers are refreshed -- how can I suppress this?