VBA Excel Overflow error

Question

Can someone please explain the fault in this code? I am getting overflow error. First i thought this might be due to large input values of n1, n2 i am giving in (order 10000), even after decreasing the values of them to few hundred's i am getting this error.

Private Sub CommandButton1_Click()       
Dim Ds1, Ds2, Ds3, Ds4, Ds5, n1, n2, n3, n4, n5, C, m, a0, w, D, N, i, Pi As Double  
Pi = 3.14159265359
Ds1 = TextBox8.Text
n1 = TextBox10.Text
Ds2 = TextBox13.Text
n2 = TextBox14.Text
Ds3 = TextBox17.Text
n3 = TextBox18.Text
Ds4 = TextBox11.Text
n4 = TextBox12.Text
Ds5 = TextBox15.Text
n5 = TextBox16.Text
C = TextBox20.Text
m = TextBox19.Text
a0 = TextBox22.Text
w = TextBox21.Text`  
D = Sqr(((n1 * (Ds1 ^ 2)) + (n2 * (Ds2 ^ 2)) + (n3 * (Ds3 ^ 2)) + (n4 * (Ds4 ^ 2)) + (n5 * (Ds5 ^ 2))) / (n1 + n2 + n3 + n4 + n5))
N = n1 + n2 + n3 + n4 + n5  

ReDim af1(N) As Double  
ReDim Y(N) As Double  

af1(0) = a0

For i = 1 To N    
Y(i) = 1.12 - (0.23 * (af1(i - 1) / w)) + (10.55 * ((af1(i - 1) / w) ^ 2)) - (21.72 * ((af1(i - 1) / w) ^ 3)) + (30.39 * ((af1(i - 1) / w) ^ 4))  
af1(i) = af1(i - 1) + (C * (D ^ m) * (Pi ^ (m / 2)) * ((af1(i - 1)) ^ (m / 2)) * (Y(i) ^ m))    
Next i      

TextBox11.Text = af1(N)
End Sub

It would help if you post the actual error and the line you get the error on. — user844705
– user844705, Commented Dec 12, 2016 at 10:41
Could you tell where exactly the error is occurring? It would help a lot. — PedroMVM
– PedroMVM, Commented Dec 12, 2016 at 10:42
Can you please check this link. I attached two screenshots. postimg.org/image/wid3b5n51 postimg.org/image/5w0mm6ixx Exact error is occuring at this line ReDim af1(N) As Double — Prudhvi Charan
– Prudhvi Charan, Commented Dec 12, 2016 at 10:50
It seems that you have not declared af1 and Y as arrays and you are using Redim instead. Change it to Dim and try again. — PedroMVM
– PedroMVM, Commented Dec 12, 2016 at 11:00
are Ds1 , Ds2 , Ds3 as Double or Variant ? if you meant all of them to be Double, you need Dim Ds1 As Double, Ds2 As Double, Ds3 As Double`, etc. — Shai Rado
– Shai Rado, Commented Dec 12, 2016 at 11:01

Excel Developers · Accepted Answer · 2016-12-12 11:01:09Z

0

Try declaring all your variable as Double. At the moment only Pi is a double. If you say

Dim D1, D2 as Double

then only D2 is a Double. D1 is a Variant. It should be

Dim D1 as Double, D2 as Double

etc.

Also cast your textbox values:

Ds1 = CDbl(TextBox8.Text)

answered Dec 12, 2016 at 11:01

Excel Developers

2,8252 gold badges24 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Prudhvi Charan Over a year ago

Dim C, m, Ds1, n1, a0, Pi, af1, i, w As Double Pi = 3.14159265359 C = TextBox13.Text m = TextBox12.Text Ds1 = TextBox8.Text n1 = TextBox10.Text a0 = TextBox14.Text w = TextBox15.Text ReDim af1(n1) As Double ReDim Y(n1) As Double af1(0) = a0 For i = 1 To n1 Y(i - 1) = 1.12 - (0.23 * (af1(i - 1) / w)) + (10.55 * ((af1(i - 1) / w) ^ 2)) - (21.72 * ((af1(i - 1) / w) ^ 3)) + (30.39 * ((af1(i - 1) / w) ^ 4)) af1(i) = af1(i - 1) + (C * (Ds1 ^ m) * (Pi ^ (m / 2)) * ((af1(i - 1)) ^ (m / 2)) * (Y(i - 1) ^ m)) Next i TextBox11.Text = af1(n1) Any idea why in this code worked?

Collectives™ on Stack Overflow

VBA Excel Overflow error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related