在VBA脚本中使用“数据分析工具”时产生的随机数生成器错误

Question

我已经创建了一个执行统计技术引导的VBA脚本。我的原始样本是通过使用excel中的数据分析工具从泊松分布中提取的(因为excel中没有用于泊松分布的逆构建函数，并且我想不出任何其他方法来模拟泊松分布的值)。然而，当我运行我的VBA脚本时，我从它的第一次迭代中得到了以下通知：

“随机数生成-输出范围将覆盖现有数据。请按”确定“覆盖范围内的数据：‘TestCoverage(Poisson，ss50).xlsmParametricBootstrap'!$B$2:$B$51”

当使用数据分析工具时，我知道“覆盖”要求，因此，在操作VBA脚本之前，我总是检查单元格是否为空。但是，除了检查它们是否清晰之外，我还在模型中加入了一个特定部分，用于在第一次使用该工具之前清除指示问题的单元格的内容(line16)，但VBA脚本仍然表现为显示此通知的单元格不为空。当VBA脚本到达line22 (数据分析工具的操作)时，它会抛出上一条消息。

奇怪的是，我已经创建了一个相同的VBA脚本(因为我必须评估一组分布的自举)，它同样使用数据分析工具从正态分布中提取原始样本，但在这种情况下它可以正常运行。

代码如下：

Sub parametricbootstrappoisson50()

Dim iterations As Integer

iterations = InputBox("Please insert the number of times you want the bootstrapping process to be performed")

Application.ScreenUpdating = False

'These statements clear the results in the Coverage Results sheet existing from previous operations of the macro
Sheets("Coverage Results").Activate
Range("A3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Sheets("Parametric Bootstrap").Activate
Range("B2:C51").Select
Selection.ClearContents

For n = 1 To iterations

    'Creates an original sample from a Poisson distribution with parameter lambda P2 (mean)
    Application.Run "ATPVBAEN.XLAM!Random", ActiveSheet.Range("$B$2"), 1, 50, _
    5, , Range("P2").Value

    '1000 bootstrap samples and bootstrap means are produced and stored in the respective cells

    For i = 1 To 1000

        'Creates a bootstrap sample from a Poisson distribution with estimated parameter lambda Q2 (mean)
        Application.Run "ATPVBAEN.XLAM!Random", ActiveSheet.Range("$B$2"), 1, 50, _
        5, , Range("Q2").Value
        Sheets("Parametric Bootstrap").Range("E2").Offset(i, 0).Value = Sheets("Parametric Bootstrap").Range("R2").Value

        'Delete the bootstrap sample produced so that it will not need confirmation to overwrite the data when the next bootstrap sample will be drawn
        Range("C2:C51").Select
        Selection.ClearContents

    Next i

    'This is the process of transferring the results of the bootstrapping process to the Coverage Results sheet
    Sheets("Coverage Results").Range("A1:F1").Offset(n, 0).Value = Sheets("Parametric Bootstrap").Range("I2:N2").Value

    'Indication on whether the population mean is covered from the bootstrap confidence intervals of the nth iteration or not
    If Sheets("Parametric Bootstrap").Range("P2").Value >= Sheets("Coverage Results").Range("C1").Offset(n, 0).Value And Sheets("Parametric Bootstrap").Range("P2").Value <= Sheets("Coverage Results").Range("D1").Offset(n, 0).Value Then
        Sheets("Coverage Results").Range("G1").Offset(n, 0).Value = "Yes"
    Else
        Sheets("Coverage Results").Range("G1").Offset(n, 0).Value = "No"
    End If

    Sheets("Parametric Bootstrap").Activate

    'Delete the original sample produced so that it will not need confirmation to overwrite the data when the next original sample will be drawn
    Range("B2:B51").Select
    Selection.ClearContents

Next n

Application.ScreenUpdating = True

Sheets("Coverage Results").Activate
Range("I2").Select

End Sub

Answer 1

这并不完全是对你问题的回答，但我有机会做过需要模拟泊松随机变量的模拟，并为此编写了我自己的用户定义函数。它的工作原理是模拟泊松过程，并计算1个单位时间内到达的数量。如果您希望它的行为像工作表函数Rand()一样，那么就从Application.Volatile中删除注释标志，并在电子表格上发生任何变化时重新计算。

Function RandPoisson(lambda As Double) As Long
    'Application.Volatile
    Dim count As Long
    Dim x As Double, sum As Double
    Do While True
        x = -Log(Rnd()) / lambda
        If sum + x > 1 Then
            RandPoisson = count
            Exit Function
        Else
            sum = sum + x
            count = count + 1
        End If
    Loop
End Function

为了测试这一点，我编写了以下sub，并将输出与内置函数Poisson.Dist进行了比较

Sub test()
    Dim i As Long, p As Long
    Dim counts As Variant

    ReDim counts(0 To 9)
    Randomize
    For i = 1 To 1000000
        p = RandPoisson(3)
        If p < 10 Then counts(p) = counts(p) + 1
    Next i
    For i = 0 To 9
        Range("B2").Offset(i).Value = counts(i) / 1000000
    Next i
End Sub

输出：

​

​

即使它涉及一百万次模拟，它也只需要大约1秒的时间来计算。