VBA rand 如何使用上界和下界生成随机数？

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

提问by user1759942

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.

所以也许这是多余的，也许这就像问为什么大多数人天生有 5 个手指一样，最后的简短答案总是：因为它就是这样而且它只是有效，但我讨厌那个答案，该死的我想知道如何VBA 中的 Rnd() 函数有效。

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

Ms Office Excel 的 MSDN 说 RND 定义为：

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

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."

“数字的值决定了 Rnd 如何生成随机数：对于任何给定的初始种子，都会生成相同的数字序列，因为对 Rnd 函数的每次连续调用都使用前一个数字作为序列中下一个数字的种子。”

followed by this:

其次是：

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

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

要生成给定范围内的随机整数，请使用以下公式：

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

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

所以例如：
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 + 1get you random number between 1 and 6, but 6 * Rnd + 5gets you a number between 5 and 10?

但这如何运作？这些数字来自哪里？为什么会6 * Rnd + 1得到 1 到 6 之间的随机数，而6 * Rnd + 5得到 5 到 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?

此外，如果 VBA 的创建者很清楚使用什么公式可以成功地将其缩小到特定范围，为什么不让 RND 函数带有可选的 Ubound 和 Lbound 参数呢？我不可能是唯一一个看着那个公式的人，这到底是什么？

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?

我突然想到这个问题可能基于数学本身。如果你取一个小整数，你会应用什么函数来使这个整数适合指定的范围……那么谁能解释一下这个公式是如何工作的？

回答by Code Different

Here's a step by step guide:

这是一个分步指南：

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

1. Rnd给出一个介于 0 和 < 1 之间的随机小数
2. 6 * Rnd给出一个介于 0 和 < 6 之间的随机小数
3. Int(6 * Rnd)这一轮下来，所以你得到0和5之间的随机值

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

在下限和上限之间生成随机数是很常见的。Excel 确实有一个RANDBETWEEN功能可以做到这一点：

Value = WorksheetFunction.RandBetween(1, 6)

---

---

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

编辑：现在让我们将其放入Lboundand Ubound（假设两者都是整数和Lbound < Ubound）

First, define:

首先，定义：

n = ubound - lbound

Next, we will rewrite the MSDN formula slightly:

接下来我们稍微改写MSDN公式：

   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;

从#3，我们知道Int(((n + 1) * Rnd)给出一个介于 0 和 n 之间的随机整数。因此，当您将该随机数添加到下限时，您会得到一个介于下限和上限之间的数字；

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

回答by Logan Reed

This may make it a bit more clear: Rnd returns a random number in 0 to <1 range (it is a Single -- aka a fraction, so you have all kinds of 0.3948, 0.001, 0.8, etc. -- uniformly distributed pseudo random numbers). The argument in the function is intended for "ease of use" in formulas and algorithms, but arguably not a good fit for a casual user.

这可能会让它更清楚一点：Rnd 返回一个 0 到 <1 范围内的随机数（它是一个单一的——也就是一个分数，所以你有各种 0.3948、0.001、0.8 等等——均匀分布的伪随机数）。函数中的参数旨在在公式和算法中“易于使用”，但可以说不适合临时用户。

In general, Rnd actually follows a de-facto standard of how random number generator API is exposed in many, if not all programming languages (i.e. generating a random fraction from 0.0 to <1.0). So if you go from VBA to Python to Java to C -- you'll see a striking resemblance in terms of how it works.

一般而言，Rnd 实际上遵循一个事实上的标准，即随机数生成器 API 如何在许多（如果不是所有）编程语言中公开（即生成从 0.0 到 <1.0 的随机分数）。因此，如果您从 VBA 到 Python 再到 Java 再到 C——您会发现其工作方式惊人的相似。

Having said that, many languages (for example, Python) provide higher level wrappers to the core Rnd function so it will be easier to use in basic scenarios. VBA is a pretty old language -- one that is rarely updated by Microsoft (it is a blessing and a curse), so if you need a helper function you'd have to do it yourself.

话虽如此，许多语言（例如 Python）为核心 Rnd 函数提供了更高级别的包装器，因此在基本场景中使用起来会更容易。VBA 是一种相当古老的语言——微软很少更新它（这是一种祝福也是一种诅咒），所以如果你需要一个辅助函数，你必须自己做。

Here what you can use instead:

这里你可以使用：

Function RndBetween(lowerbound As Int, upperbound As Int) As Int
    RndBetween = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
End Function

And in your code it becomes simple:

在您的代码中，它变得简单：

RndBetween(1, 6)

After some time you'll have a module of the helper functions that will make life easier and code much more readable. Until then... StackOverflow :-)

一段时间后，您将拥有一个辅助函数模块，这将使生活更轻松，代码更具可读性。在那之前...... StackOverflow :-)

回答by Vityata

using the formula like RndBetween like make_random_old = CLng((up - down + 1) * Rnd + down)

使用像 RndBetween 这样的公式 make_random_old = CLng((up - down + 1) * Rnd + down)

It is extremely wrong (30 minutes debugging of my life was the price to understand it!) :)

这是非常错误的（我生命中 30 分钟的调试是理解它的代价！）:)

You can check why, using the Sub TestMe here:

您可以在此处使用 Sub TestMe 检查原因：

Public Sub testme()

    Dim l_counter       As Long
    Dim l_random        As Long

    For l_counter = 0 To 10000
        l_random = make_random_old(0, 2)
        If l_random = 3 Then Debug.Print l_random
    Next l_counter

    Debug.Print "END"

End Sub

Public Function make_random(down As Long, up As Long) As Long

    make_random = CLng((up - down + 1) * Rnd + down)

    If make_random > up Then make_random = up
    If make_random < down Then make_random = down

End Function

Public Function make_random_old(down As Long, up As Long) As Long

    make_random_old = CLng((up - down + 1) * Rnd + down)

End Function

As a workaround for some random in RndBetweem, use make_random- the second function in the code. It will save you lots of problems. :)

作为 RndBetweem 中某些随机的解决方法，请使用make_random- 代码中的第二个函数。它会为你省去很多问题。:)

回答by Romantic Electron

@Code Different has the correct answer already.This is just for people who don't get it on the first glance

@Code Different 已经有了正确的答案。这只是给那些第一眼没看懂的人用的

1. Excel only generates a random number between 0 to 1
2. By multiplying the random number generated in step 1 by a number xwe can get a value between 0and x

1. Excel只生成0到1之间的随机数
2. 通过将步骤 1 中生成的随机数乘以一个数字，x我们可以得到一个介于0和x

So how does

那么如何

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

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

generate a random number between upper and lower bound

在上下限之间生成一个随机数

1. In the last step you provided the number xto which the random number generated in step 1 was multiplied.Here,to generate this number xyou do upperbound - lowerboundso that a random_numberfrom 0to the difference of two numbers i.e (upperbound - lowerbound) is produced.Now if you add the smaller number i.e. lowerboundto random_numberit will generate a sum greater than the lowerbound& lesser than upperbound

3. 在最后一步，你提供的号码x，其在步骤1中生成的随机数是multiplied.Here，产生这个数字x你做upperbound - lowerbound这样一个random_number从0两个数的差即（upperbound - lowerbound）是produced.Now如果添加小number ie lowerboundto random_numberit 将产生一个大于lowerbound& 小于的总和upperbound