Repeating random numbers in VBA

Question

I have a Monte Carlo simulation in VBA. The client wants (dont question why) to fix random number sequence, i.e. every time you run the model, sequence shall stay the same. I managed to fix random seed as described here. BUT it is not the same on different PCs. Any idea why and how can I also fix it on different machines?

Why not just create a sequence of random numbers and save the sequence for use in your application? — whytheq, Apr 07 '14 at 09:43
And this is perfectly logical - the clients want the Monte Carlo simulation to be repeatable. — brettdj, Apr 07 '14 at 09:54
Or you could, using **VBA** , code your own pseudo-random number generator. — Gary's Student, Apr 07 '14 at 11:00
@Gary'sStudent If have an example of code in VBA for random number generation I would be very thankful to u — NeatNerd, Apr 07 '14 at 11:19
possible duplicate of [Repeating random variables in VBA](http://stackoverflow.com/questions/2884972/repeating-random-variables-in-vba) — brettdj, Apr 08 '14 at 01:59

Sam · Accepted Answer · 2014-04-07T11:47:12.973

You can use the rnd function with a negative argument to achieve a repeating list of random numbers.

Here is a link to the documentation:

http://office.microsoft.com/en-us/access-help/rnd-function-HA001228901.aspx

Note To repeat sequences of random numbers, call Rnd with a negative argument immediately before using Randomize with a numeric argument. Using Randomize with the same value for number does not repeat the previous sequence.

Sub TestRandomNumberSequence()

rnd (-10)

    For i = 1 To 5

        Randomize 10
        MsgBox BetweenRange(1, 20, rnd)

    Next i

    'always returns the following sequence

    '5
    '18
    '19
    '6
    '17

End Sub

Function BetweenRange(min As Integer, max As Integer, ByVal rnd As Double) As Integer

BetweenRange = Int((max - min + 1) * rnd + min)

End Function

Hmm, true, but strange, that I get different number on different computers, must be error in my code. Thx — NeatNerd, Apr 07 '14 at 13:57
This is the same answer already included in http://stackoverflow.com/questions/2884972/repeating-random-variables-in-vba — brettdj, Apr 08 '14 at 01:59

score 1 · Answer 2 · answered Apr 07 '14 at 11:29

1

As per your request, please checkout the following link:

Wabash College Download

answered Apr 07 '14 at 11:29

Gary's Student

95,722
10
59
99

Thx for that too, but it uses RND method from VBA too I think – NeatNerd Apr 07 '14 at 14:05

Repeating random numbers in VBA

2 Answers2