Run time error '6' : Overflow
Hello, I when trying to run this macro:
Sub ROICalculator()
Dim Iterations As Long
Dim i As Long
Dim ii As Long
Dim iii As Long
Dim N As Long
Dim ObROI As Double
Dim eROInum As Double
Dim eROIden As Double
Dim eROI As Double
Dim x As Double
Dim y As Double
Dim z As Double
Dim PY1 As Double
Dim PY2 As Double
Dim PY3 As Double
Dim PY4 As Double
Dim PY5 As Double
Dim PY6 As Double
Dim PY7 As Double
Dim PY8 As Double
Dim PY9 As Double
Dim PYmin As Double
Dim P1 As Double
Dim P2 As Double
Dim P3 As Double
Dim P4 As Double
Dim P5 As Double
Dim P6 As Double
Dim P7 As Double
Dim P8 As Double
Dim P9 As Double
Dim Pmin As Double
Dim LB As Double
Dim UB As Double
Dim a As Long
Dim b As Double
Iterations = Range("C2").Value
N = Range("C4").Value
ObROI = Range("C3").Value
PY1 = Range("L3").Value
PY2 = Range("L4").Value
PY3 = Range("L5").Value
PY4 = Range("L6").Value
PY5 = Range("L7").Value
PY6 = Range("L8").Value
PY7 = Range("L9").Value
PY8 = Range("L10").Value
PY9 = Range("L11").Value
PYmin = Range("L12").Value
P1 = Range("M3").Value
P2 = Range("M4").Value
P3 = Range("M5").Value
P4 = Range("M6").Value
P5 = Range("M7").Value
P6 = Range("M8").Value
P7 = Range("M9").Value
P8 = Range("M10").Value
P9 = Range("M11").Value
Pmin = Range("M20").Value
LB = Range("E59").Value
UB = Range("e58").Value
For iii = 1 To iii = 101
b = 0
For ii = 1 To ii = Iterations
z = 0
For i = 1 To i = N
x = Rnd
Select Case x
Case Is > P1
y = PY1 * ((1 + (iii - 59) / 100) / 0.918)
Case Is > P2
y = PY2 * ((1 + (iii - 59) / 100) / 0.918)
Case Is > P3
y = PY3 * ((1 + (iii - 59) / 100) / 0.918)
Case Is > P4
y = PY4 * ((1 + (iii - 59) / 100) / 0.918)
Case Is > P5
y = PY5 * ((1 + (iii - 59) / 100) / 0.918)
Case Is > P6
y = PY6 * ((1 + (iii - 59) / 100) / 0.918)
Case Is > P7
y = PY7 * ((1 + (iii - 59) / 100) / 0.918)
Case Is > P8
y = PY8 * ((1 + (iii - 59) / 100) / 0.918)
Case Is > P9
y = PY9 * ((1 + (iii - 59) / 100) / 0.918)
Case Is > Pmin
y = PYmin * ((1 + (iii - 59) / 100) / 0.918)
Case Else
y = 0
End Select
z = z + y
Next i
If z > LB And z < UP Then
a = 1
Else
a = 0
End If
b = b + a
Next ii
eROInum = eROInum + ((b / Iterations) * Cells(iii + 2, 9).Value) * (iii / 100)
eROIden = eROIden + ((b / Iterations) * Cells(iii + 2, 9).Value)
Next iii
eROI = eROInum / eROIden
Cells(7, 3) = eROI
End Sub
I got the following error message: Run time error '6' : Overflow
With the debugger highlighting the line: eROI = eROInum / eROIden
Could someone help me with this please?
I have no programming experience so I'm sorry if there is a massively noob mistake in there ^^
RE: Run time error '6' : Overflow
Hi,
You get an error on this line eROI = eROInum / eROIden because the actual equation is eROI = 0 / 0.
The usual error when you try to division by zero is Run-time error '11': Division by zero. I don't know why you get '6' but the most important to you is how to avoid any error. In your case the problem is with the wrong syntax of For... Next loop.
In your code: For iii = 1 To iii = 101
The correct syntax is: For iii = 1 To 101
This is the reason why the code jump to line eROI = eROInum / eROIden before your variables to get some values. If you curious what is happen in the line For iii = 1 To iii = 101:
1) the variable iii is set to value 1;
2) the part iii = 101 is treated as boolean expression and because iii is already equal to 1 returns False;
3) False is treated as 0 in expressions which involve numbers and that you actually have For iii = 1 To 0 which prevents to step into the loop.
Correct the other For... Next loops you have.
Of course, based on other values you use in your code it is possible to get the same error although you steps into loops. There is a chance variable b to be equal to zero even after looping. My point is to wrap the line eROI = eROInum / eROIden in some checking structure, for example:
If eROIden = 0 Then
eROI = 0
Else
eROI = eROInum / eROIden
End If
If there is something else - ask.
Best regards.
Thx a lot for the very
Thx a lot for the very informative reply. I have corrected the mistakes you found and run the macro. It is currently running and it might take a long time as it needs to simulate 101*10000*5000 random payoffs. However the Overflow error seems to be gone!
I will let you know when it finishes running if everything went smoothly.
Thx again!
PS: How do I give u points for your answer? (If that's a thing in this forum)
update
Everything seems to run as desired!
update
If z > LB And z < UP Then
In this line I have already replaced UP with UB (mistype)
for an overflow error, most
for an overflow error, most likely you're dimming a variable incorrectly... if u change all to "variant" (not recommended but lets try), does it work ?
I tried dimming all variables
I tried dimming all variables as variant. But unfortunately I get the same error.
I tried inserting this:
eROInum = 0.1
eROIden = 0.1
Just before the loops and now I get a fixed output of 1 for the sub (output is eROInum/eROIden) as if it was completely skipping the loops (they are meant to determine the values of eROInum and eROIden).