excel - 如何在 VBA 中调试平方根溢出（MacOS v.16.36 上的错误）

Question

我是 VBA 的菜鸟，在 VBA 中使用 sqr() 时遇到问题。每当我在 VBA 中使用 Sqr() 函数时，在 SigSqrdt = sigma * Sqr(dt) 行上会出现一个消息框“运行时错误'6'溢出”。我试图只模拟布朗运动。

Sub Simulating_Geometric_Brownian_Motion()
Dim T, S, mu, sigma, dt, SigSqrdt, LogS, drift, i, N
T = InputBox("Enter the length of the time period (T)")
N = InputBox("Enter the number of periods (N)")
S = InputBox("Enter the initial stock price (S)")
mu = InputBox("Enter the expected rate of return (mu)")
sigma = InputBox("Enter the volatility (sigma)")
dt = T / N
SigSqrdt = sigma * Sqr(dt)
drift = (mu - 0.5 * sigma * sigma) * dt
LogS = Log(S)
ActiveCell.Value = "Time"
ActiveCell.Offset(0, 1) = "stock price"
ActiveCell.Offset(1, 0) = 0  ' beginning time
ActiveCell.Offset(1, 1) = S 'beginning stock price
For i = 1 To N
ActiveCell.Offset(i + 1, 0) = i * dt
LogS = LogS + SigSqrdt * RandN()
ActiveCell.Offset(i + 1, 1) = Exp(LogS)
Next i
End Sub

Function RandN()
RandN = Application.NormSInv(Rnd())
End Function

样本输入：

T=1000, N=1000, S=10, mu=10, sigma=1

标签： excelvbainteger-overflow