How does VBA rand generate random numbers using the upperbound and lowerbound?

Question

so maybe this is redundant, maybe it's like asking why most humans are born with 5 fingers, the short answer in the end is always: because that's how it is and it just works, but I hate that answer and dammit I want to know how Rnd() function in VBA works.

The MSDN for Ms Office Excel says that RND is defined as:

Rnd[(number)] 'The optional number argument is a Single or any valid numeric expression.

It goes on to say

"The value of number determines how Rnd generates a random number: 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."

followed by this:

To produce random integers in a given range, use this formula:

Int((upperbound - lowerbound + 1) * Rnd + lowerbound)

so for example:
Dim MyValue MyValue = Int((6 * Rnd) + 1) ' Generate random value between 1 and 6.

But how does that work? where are these numbers coming from? why does 6 * Rnd + 1 get you random number between 1 and 6, but 6 * Rnd + 5 gets you a number between 5 and 10?

furthermore, if it was so apparent to the creators of VBA what formula to use to successfully narrow this down to a specific range, why not just have the RND function come with optional Ubound and Lbound arguments? I can't be the only one looking at that formula going what in the world is that?

At the end of the day it works of course fine for any of my pseudo random number needs and maybe I'm looking a gift horse in the mouth but still!

---

EDIT

---

It occurs to me that this question might be based in Math itself. if you take a small integer what functions do you apply to make that integer fit in a specified range.. so can anyone explain how this formula works?

Answer 1

Here's a step by step guide:

1. Rnd gives a random decimal between 0 and < 1
2. 6 * Rnd gives a random decimal between 0 and < 6
3. Int(6 * Rnd) round it down so you get a random value between 0 and 5

It's very common to generate a random number between a lower and upper bound. Excel does have a RANDBETWEEN function to do this:

Value = WorksheetFunction.RandBetween(1, 6)

---

Edit: now let's fit that into Lbound and Ubound (assuming both are integers and Lbound < Ubound)

First, define:

n = ubound - lbound

Next, we will rewrite the MSDN formula slightly:

   Int((ubound - lbound + 1) * Rnd + lbound)
== Int((ubound - lbound + 1) * Rnd) + lbound
== Int(((n + 1) * Rnd)              + lbound

From #3, we know that Int(((n + 1) * Rnd) gives a random integer between 0 and n. So when you add that random number to the lowerbound, you get a number between the lowerbound and the upperbound;

   Int(((n + 1) * Rnd) + lbound
== 0...n               + lbound
== lbound...ubound