0

I am a rookie of VBA that I have a problem to use sqr() in VBA. Whenever I use Sqr() function in VBA, A message box appear" Run-time error'6' Overflow" on SigSqrdt = sigma * Sqr(dt) row. And I am trying to simulate the Brownian Motion only.

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

Sample inputs:

T=1000, N=1000, S=10, mu=10, sigma=1
Improve this question
16
  • 2
    What are your inputs? Commented Apr 29, 2020 at 15:38
  • In SigSqrdt = sigma * Sqr(dt) the overflow is likely to be the * rather than Sqr(dt). Also, why not declare the types of the variables? Commented Apr 29, 2020 at 15:40
  • 1
    Your code works fine for me when I give it reasonable inputs. Others really can't help you unless you give enough information for them to reproduce the problem. Please answer the question that @BigBen asked. Commented Apr 29, 2020 at 15:46
  • 3
    Indent your code. Add Option Explicit, and declare your variables. If you declare Dim SigSrdt As Double it will hold a fairly large value but it could still overflow when using very large numbers. NOTE: The largest number a double can hold is 1.79769313486231570 • 10^308 Commented Apr 29, 2020 at 16:08
  • 1
    That code with those inputs does not lead to the bug that you are reporting. Something else is happening here, something which likely involves code that you haven't shown. Please read about the importance of having a minimal reproducible example. Commented Apr 29, 2020 at 16:24
Related questions
0
Integer Overflow in VBA project
15
Overflow when multiplying Integers and assigning to Long
2
integer overflow in vba even though I'm using long data type
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.