random number issue

**Roshan.Shakya** · 01-23-2021, 08:37 PM

Hi Gurus,

I am using Rnd() function to generate random number in a loop; however, every time I run macro, the same random numbers are repeated. For clarity, I would like to post the following first 3 random numbers which repeat in the same positions.

1. 0.705547511577606
2. 0.533424019813538
3. 0.579518616199493

and so on.

My loop looks like this:

Sub Random()

Dim R as Double, i as Integer

For i = 1 to 1000
     R = Rnd() '(1st = 0.705547511577606, 2nd = 0.533424019813538, 3rd = 0.579518616199493)
     other codes ...............
Next i

End Sub

My result should be different due to the use of random number but are in fact same. Is it a default pattern to generate random number? I was anticipating random number in every macro run with no repetitions. I appreciate your guidance.

Thank you
Roshan

**mehmetcik** · 01-23-2021, 08:55 PM


Sub Random()

Dim R as Double, i as Integer

For i = 1 to 1000

     Randomize 10

     R = Rnd() '(1st = 0.705547511577606, 2nd = 0.533424019813538, 3rd = 0.579518616199493)
     other codes ...............
Next i

End Sub

**Roshan.Shakya** · 01-23-2021, 09:07 PM

Thank you mehmetcik,

Randomizing also gives the same pattern. The first 3 following random numbers (for simplicity) are same in every macro run.

1. 0.574993312358856
2. 0.492133736610413
3. 0.271870911121368

and so on ...

I require different random numbers in every macro run. Is there a way to do it?

Thank you
Roshan

**hrlngrv** · 01-23-2021, 09:24 PM

Either use Randomize without an argument, or use Randomize Application.WorksheetFunction.Randbetwwen(1,999999).

That said, worksheet RAND() and RANDBETWEEN() use much better pseudorandom number generators than VBA's Rnd. If I recall correctly, VBA's Rnd uses an 1970s era linear congruential generator. MSFT updated Excel's worksheet RAND() to reflect a few extra decades of numerical programming research in Excel 2003 and more in Excel 2007.

If you need to do serious Monte Carlo simulations, you should be using a real stats package rather than ANY spreadsheet, but if you have to use Excel, you'd actually be much better off using Application.WorksheetFunction.Rand() and consigning VBA's Rnd to the oblivion it deserves.

**Roshan.Shakya** · 01-23-2021, 10:59 PM

Thank you hrlngrv,

I found worksheet RAND() better alternative for my Monte Carlo Simulation. Thank you for sharing this valuable knowledge.

Roshan

**huuthang_bd** · 01-23-2021, 09:30 PM

Try this

Sub Random()

Dim R As Double, i As Integer
Randomize
For i = 1 To 1000
     R = Rnd() 
     'other codes ...............
Next i

End Sub

**Roshan.Shakya** · 01-23-2021, 11:00 PM

Thank you all for sharing your valuable time and knowledge. I really appreciate your advices.

Roshan

random number issue

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

random number issue

Re: random number issue

Re: random number issue

Re: random number issue

Re: random number issue

Re: random number issue

Re: random number issue

Thread Information

Users Browsing this Thread

Similar Threads

Random issue when running macro

Random Number - Random Selection

[SOLVED] Random numbers but each number not eqully random.

IF statement to evaluate result of random number generator to generate next random number

[SOLVED] Generate random 6-16-byte random hexadecimal number

Generating a Random Number of Random Numbers

[SOLVED] How do I find random number in list of random alpha? (Position is.

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions