excel RandBetweenInt always give same result

[caabdul]

I have to generate a random number using formula RandBetweenInt. Problem is that I get same sequence of random numbers generated.

Please see attached file > Sheet1 > Cell B2

Generating random numbers always create a list 108, 117, 59, 61, 3

Even if I save file and close and re-open it, it will generate random number same as before 108, 117, 59, 61, 3 and so on.

Why excel doesn't create random numbers that are not preset?

Regards

[MrShorty]

I don't know how well this behavior is documented, but it has long been a property of VBA's random number generator. The random number generator will generate the same "random" sequence, unless you use the Randomize statement to reseed the random number generator. https://msdn.microsoft.com/en-us/vba...mize-statement

The solution to your query should be as simple as adding a randomize statement to the UDF, or have an open event procedure the executes a Randomize statement to properly initialize VBA's random number generator.

[joeu2004]

I think the behavior is very well documented. From the Randomize help page:

Randomize uses number to initialize the Rnd function's random-number generator, giving it a new seed value. If you omit number, the value returned by the system timer is used as the new seed value.

If Randomize is not used, the Rnd function (with no arguments) uses the same number as a seed the first time it is called, and thereafter uses the last generated number as a seed value.

From the Rnd help page:

If number is............. Rnd generates
Less than zero......... The same number every time, using number as the seed.
Greater than zero.... The next random number in the sequence.
Equal to zero.......... The most recently generated number.
Not supplied........... The next random number in the sequence.
[....]
For any given initial seed, the same number sequence is generated because each successive call to the Rnd function uses the previous number as a seed for the next number in the sequence.

Before calling Rnd, use the Randomize statement without an argument to initialize the random-number generator with a seed based on the system timer.

[caabdul]

All I need is a formula that generate different sequence of random numbers from 1 to 201 every time it is run, excluding the numbers given in a range.

May be NOW() can influence it some way.

[protonLeah]

Per #2

Please Login or Register  to view this content.

[caabdul]

I have worked out a formula, I don't want to use a macro.

This formula seems working
=ROUND(((upperlimit-lowerlimit)+1)*MOD(NOW(),0.0001)*10000,0)
Where upperlimit is 201 and lowerlimit is 1 in my case. It seems working perfectly (however not able to exclude numbers without a macro) to generate a random number with varying sequence every time.

[protonLeah]

Why is that formula preferred to RANDBETWEEN(lowerlimit,upperlimit)?

[caabdul]

Thanks MrShorty, joeu2004, protonLeah

Sorry I didn't know RandBetweenInt was a set function in my file. I am using this file a long time and I thought RandBetweenInt was a standard excel formula, lols.

Anyways, Randomize works for me in macro.