VBA 函数，它基于随机分布样本生成相同的数字

Question

我希望这个过程在多个单元格中产生相同的数字。我希望错误项保持不变。我是 VBA 的新手，但我假设有一行代码会告诉它不要使用一组新的随机值运行公式。

Option Base 1
Function EuropeanOptionMonteCarlo(c_ As String, s As Double, x As Double, t As Double, z As Double, r_ As Double, q As Double, n As Double, nIter As Double) As Variant
Dim dt, e, dlns, price, SimVar(), PayVar() As Double
ReDim SimVar(nIter, n + 1)
ReDim PayVar(nIter)
dt = t / n
a = 0
For i = 1 To nIter
    SimVar(i + a, 1) = s
    Randomize
    p = 0
    For j = 1 To n
        If (j - 1) / 365 - Int((j - 1) / 365) = 0 And j > 1 Then p = p + 1
        e = WorksheetFunction.NormSInv(Rnd())
        dlns = (r_ - q - z ^ 2 / 2) * dt + z * e * dt ^ 0.5
        If j - 365 * p = 1 And p > 0 Then
            SimVar(i + p + a, 2) = SimVar(i + p - 1 + a, 366) * Exp(dlns)
        Else
            SimVar(i + p + a, j - 365 * p + 1) = SimVar(i + p + a, j - 365 * p) * Exp(dlns)
        End If
    Next j
    If c_ = "C" Then
        PayVar(i) = WorksheetFunction.Max(SimVar(i + p + a, j - 365 * p) - x, 0) * Exp(-r_ * t)
    ElseIf c_ = "P" Then
        PayVar(i) = WorksheetFunction.Max(x - SimVar(i + p + a, j - 365 * p), 0) * Exp(-r_ * t)
    End If
    a = a + p
Next i
price = 0
temp = 0
For i = 1 To nIter
    price = price + PayVar(i)
Next i
price = price / nIter
EuropeanOptionMonteCarlo = price
End Function

Answer 1

VBA 文档位于 https://msdn.microsoft.com/en-us/library/office/gg264511.aspx

这是如何重复序列的关键引用

注意
要重复随机数序列，请使用负数调用 Rnd 参数，然后立即将随机化与数值参数一起使用。 使用具有相同数值的随机化不会重复 上一个序列。

这是一些代码

Sub Test()
    Dim v
    v = Rnd(-1)
    Dim lRand As Long
    lRand = RandomNumber
    Debug.Assert lRand = 35  '* for me always returns 35
End Sub
Function RandomNumber() As Double
    RandomNumber = Int(1000 * Rnd(1))
End Function