Repeatable sequences of random numbers

[hanron]

Hello,

I'm using Excel 2007 on Win XP.

I would like to be able to repeat a sequence of random numbers generated using rnd debug/ repeat scenarios in a model I am building.

Currently, I am using the following to set the seed to try and accomplish this (via a Sub that I call from a button form to "restart" the random number generation from the beginning)

Rnd (-1)
Randomize (10)

This works nicely enough until I close excel and reopen it again. The next time I run this code the list of random numbers produced is different from the previous instance of excel. However, it will be consistent until I close excel and reopen it again.

Would appreciate any ideas on what I might be doing wrong.

Thank you,
Han-Ron

 

[Richard Schollar]

Given the explanation in VBA help I would expect what you are doing to produce the results you want (but not what you are getting!) - as a work around, maybe you should store the random number sequence in a worksheet range then reference this in future?

 

[AlphaFrog]

Try putting the Randomize statment before the Rnd statement.

Randomize (10)
Rnd(-1)

 

[hanron]

@Richard - thanks, I'll try that if I can't find a better solution.

@AlphaFrog - thanks, I just tried it but no luck.

I have a feeling that there's some sort of randomize initialization that is happening when Excel starts up - I've seen references to a Randomize setting in the INI file for older versions of Excel, but haven't been able to find any documentation for anything similar in Excel 2007 to see if I can turn it off. Anyone know?

Han-Ron

 

[Rick Rothstein]

I just tried this macro...

Sub TestRandomize()
  Randomize 10
  Debug.Print Rnd(-1)
  Debug.Print Rnd
  Debug.Print Rnd
End Sub

in my saved copy of XL2007, then closed Excel, reopened it and ran the macro again and got the same results as I did before I closed/reopened it, so the procedure does work as expected. I'm not sure why you are not getting it to work for you.