我正在创建一个美式期权的临界值近似器。我在大约40个循环后才得到错误"#Value！"（用计数器跟踪）。
经过一番尝试和错误，我意识到它来自于调用BlackScholes定价函数的循环部分。理论上，我希望在Black-Scholes欧洲价格计算中，在保持其他变量固定的情况下，迭代运行现货价格的一系列值。经过修改，我将问题归结为这样一个事实，即在第一个循环之后，它不再像我在迭代中使用该值那样计算Black-Scholes，而我得到的值只增加了1，然后由于一些不明显的原因，在40个错误值的循环后，它就消失了。
因此，下面我将代码截断为一个非常简单的框架，这就是我的问题的本质。如有任何帮助，我们将不胜感激。
Function Looper(S As Double, K As Double, r As Double, t As Double, q As Double, Vol As Double) As Double
Dim i As Double
For i = 100 To 150 Step 1#
MsgBox i
MsgBox BS(i, K, r, t, q, Vol, "Call") 'After the first loop the values are wrong,
'What I'd like is, BS(100,...), BS(101,...),BS(102,...) which it is not.
'Not sure what it's actually calculating, since the values are way off
Next i
End Function
Public Function BS(S As Double, K As Double, r As Double, t As Double, q As Double, Vol As Double, CP As String) As Double
Dim volrootime As Double
Dim d1 As Double
Dim d2 As Double
Dim DiscF As Double
Dim DivF As Double
Dim topline1 As Double
Dim topline2 As Double
Dim topline As Double
Dim Price As Double
t = t / 365
r = r / 100
q = q / 100
DiscF = Exp(-r * t)
DivF = Exp(-q * t)
volrootime = (t ^ 0.5) * Vol
topline1 = Log(S / K)
topline2 = ((r - q) + ((Vol ^ 2) / 2)) * t
topline = topline1 + topline2
d1 = topline / volrootime
d2 = d1 - volrootime
If CP = "Call" Then
' Theta is in terms of Calendar days, changing the denominator to 252 changes it to trading days
Price = (S * DivF * Bign(d1)) - (K * DiscF * Bign(d2))
Else
' Theta is in terms of Calendar days, changing the denominator to 252 changes it to trading days
Price = K * DiscF * Bign(-d2) - S * DivF * Bign(-d1)
End If
BS = Price
End Function
每次调用BS函数时，r、t、q的值都会发生变化。如果它们必须保持不变，那么应该在BS函数声明中使用ByVal，如下所示：
BS(S As Double, K As Double, ByVal r As Double, ByVal t As Double, ByVal q As Double, ...
默认情况下，参数是通过引用传递的，被调用函数中的任何更改都会反映在调用函数中。
顺便说一句，在这个例子中，我在调试时不会使用消息框，而是使用debug.print，如下所示：
Debug.Print "i=" & i & vbTab & "BS=" & BS(i, K, r, t, q, Vol, "Call")
在按Ctl+G（转到）打开的窗口中进行打印。