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
– whytheq, Commented Apr 7, 2014 at 9:43
And this is perfectly logical - the clients want the Monte Carlo simulation to be repeatable. — brettdj
– brettdj, Commented Apr 7, 2014 at 9:54
Or you could, using VBA , code your own pseudo-random number generator. — Gary's Student
– Gary's Student, Commented Apr 7, 2014 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
– NeatNerd, Commented Apr 7, 2014 at 11:19

Sam · Accepted Answer · 2014-04-07 11:47:12Z

3

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

edited Apr 7, 2014 at 11:47

answered Apr 7, 2014 at 11:38

Sam

7,3333 gold badges27 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

NeatNerd Over a year ago

Hmm, true, but strange, that I get different number on different computers, must be error in my code. Thx

brettdj Over a year ago

This is the same answer already included in stackoverflow.com/questions/2884972/…

Gary's Student · Accepted Answer · 2014-04-07 11:29:53Z

1

As per your request, please checkout the following link:

Wabash College Download

answered Apr 7, 2014 at 11:29

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

1 Comment

NeatNerd Over a year ago

Thx for that too, but it uses RND method from VBA too I think

Collectives™ on Stack Overflow

Repeating random numbers in VBA

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related