我可以使用API​​或其他方式接近VBA中的TRUE RANDOM NUMBER吗? [英] Can I use API or something else to get close to a TRUE RANDOM NUMBER in VBA?

问题描述

我可以在VBA中访问API，库或其他基于芯片集的命令吗?

Is there an API, library, or some other chip-set based command I can access in VBA?

我目前有一个获取随机数的设置；但是，当我将结果集进行测试时，这些数字甚至不能接近生成良好的统计曲线.我通过生成600个2个六面骰子的模拟卷进行了测试，每次将2个骰子加在一起.我希望7号能取得巨大的领先.但是，它两次获得第二名，并且没有创建合适的统计曲线.

I currently have a setup for getting random numbers; however, when I put the result sets to the test, the numbers do not even come close to generating a good statistical curve. I tested this by generating 600 simulated rolls of 2 six-sided dice totaling the 2 dice together each time. I was hoping for the number 7 to take a huge lead; however it came in second twice with nowhere near the appropriate statistical curve being created.

我当前的代码使用标准的VBA方法，但是正如我所说的，统计测试失败:

My current code uses the standard VBA method, but as I said fails the statistics test:

Randomize
GetRandomNo = Int((6 * RND) + 1)

推荐答案

一个相当完整的答案:

有多种生成随机数的方法，但是一种方法是使用Windows API进行繁重的工作.Windows具有API函数来生成加密安全的随机字节，并且这些函数可以利用硬件随机数提供程序.

There are numerous ways to generate random numbers, but one way is to use the Windows API to do the heavy lifting. Windows has API functions to generate cryptographically secure random bytes, and these functions can make use of hardware random number providers.

首先，我们声明API函数:

First, we declare the API functions:

Public Declare PtrSafe Function BCryptOpenAlgorithmProvider Lib "bcrypt.dll" (ByRef phAlgorithm As LongPtr, ByVal pszAlgId As LongPtr, ByVal pszImplementation As LongPtr, ByVal dwFlags As Long) As Long
Public Declare PtrSafe Function BCryptGenRandom Lib "bcrypt.dll" (ByVal hAlgorithm As LongPtr, pbBuffer As Any, ByVal cbBuffer As Long, ByVal dwFlags As Long) As Long
Public Declare PtrSafe Function BCryptCloseAlgorithmProvider Lib "bcrypt.dll" (ByVal hAlgorithm As LongPtr, ByVal dwFlags As Long)

然后，我们使用此调用，并使用模数将我们的数量减少到所需范围内的一个:

Then, we use this call, and use modulus to reduce our number to one in the desired range:

Public Function RandomRangeWinApi(Lower As Long, Upper As Long) As Long
    Dim hAlg As LongPtr
    Dim iAlg As String
    iAlg = "RNG" & vbNullChar
    BCryptOpenAlgorithmProvider hAlg, StrPtr(iAlg), 0, 0
    Dim lRandom As Long
    BCryptGenRandom hAlg, lRandom, LenB(lRandom), 0
    RandomRangeWinApi = Abs(lRandom) Mod (Upper - Lower + 1) + Lower
    BCryptCloseAlgorithmProvider hAlg, 0
End Function

如果您假设整数具有无限范围的值，则此方法很好.但是，事实并非如此，这意味着在一定程度上是不精确的.同样，乘法假设一个无穷大的精确数字，这也不是正确的，并且会引起轻微的偏差.

This approach is fine if you assume an integer has an infinite range of values. However, it hasn't, which means at the limits it's imprecise. Similarly, multiplication assumes an infinitely precise number, which also isn't true and causes a slight bias.

我们可以通过直接使用数字的二进制表示形式并丢弃此模板之外的数字来解决此问题:

We can get around this by directly using the binary presentation of numbers, and discarding numbers that fall outside of this template:

Public Function RandomRangeExact(Lower As Long, Upper As Long) As Long
    'Initialize random number generator
    Dim hAlg As LongPtr
    Dim iAlg As String
    iAlg = "RNG" & vbNullChar
    BCryptOpenAlgorithmProvider hAlg, StrPtr(iAlg), 0, 0
    'Initialize bit template
    Dim template As Long
    Dim i As Long
    Do While template < Upper - Lower
        template = template + 2# ^ i
        i = i + 1
    Loop
    Dim lRandom As Long
    Do
        'Generate random number
        BCryptGenRandom hAlg, lRandom, LenB(lRandom), 0
        'Limit it to template
        lRandom = lRandom And template
    Loop While lRandom > (Upper - Lower) 'Regenerate if larger than desired range (should happen less than 50% of times)
    RandomRangeExact = lRandom + Lower
    BCryptCloseAlgorithmProvider hAlg, 0
End Function

现在，让我们研究解决方案的性能以及掷骰子的机会:我已经为1到6之间的每种方法模拟了100000个随机数.

Now, let's investigate the performance of your solution and both opportunities for rolling dice: I've simulated 100000 random numbers for each approach between 1 and 6.

这是结果:

虽然第一种方法似乎在数字之间存在较大的差异(特别是较少的数字和更多的二进制数)，但对于大多数应用程序，我认为第一种方法足够准确.

While the first approach seems to have larger variance between numbers (specifically less ones and more twos), for most applications I'd assume the first is accurate enough.

这篇关于我可以使用API​​或其他方式接近VBA中的TRUE RANDOM NUMBER吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！