VBA overflow error

[danielrdgz10]

Please i need help with this program for some reason I am getting an overflow error in both functions and I dont know why could someone aid me with this?

Sub eqn()
With ThisWorkbook.Sheets("Sheet1")
Dim y1 As Variant
Dim a As Variant
Dim b As Variant
Dim c As Variant
Dim d As Variant
Dim e As Variant
Dim f As Variant
Dim g As Variant
Dim h As Variant
Dim i As Variant
Dim j As Variant
Dim k As Variant
Dim l As Variant
Dim m As Variant
Dim n As Variant
Dim o As Variant
Dim p As Variant
Dim q As Variant
Dim r As Variant
Dim s As Variant
Dim t As Variant
Dim y2 As Variant
Dim a1 As Variant
Dim b1 As Variant
Dim c1 As Variant
Dim d1 As Variant
Dim e1 As Variant
Dim f1 As Variant
Dim g1 As Variant
Dim h1 As Variant
Dim i1 As Variant
Dim j1 As Variant
Dim k1 As Variant
Dim l1 As Variant
Dim m1 As Variant
Dim n1 As Variant
Dim o1 As Variant
Dim p1 As Variant
Dim q1 As Variant
Dim r1 As Variant
Dim s1 As Variant
Dim t1 As Variant
Dim x As Variant
Dim xinitial As Variant
Dim xfinal As Variant
Dim dx As Variant

a = .Cells(2, 1).Value
a1 = .Cells(3, 1).Value
b = .Cells(2, 2).Value
b1 = .Cells(3, 2).Value
c = .Cells(2, 3).Value
c1 = .Cells(3, 3).Value
d = .Cells(2, 4).Value
d1 = .Cells(3, 4).Value
e = .Cells(2, 5).Value
e1 = .Cells(3, 5).Value
f = .Cells(2, 6).Value
f1 = .Cells(3, 6).Value
g = .Cells(2, 7).Value
g1 = .Cells(3, 7).Value
h = .Cells(2, 8).Value
h1 = .Cells(3, 8).Value
i = .Cells(2, 9).Value
i1 = .Cells(3, 9).Value
j = .Cells(2, 10).Value
j1 = .Cells(3, 10).Value
k = .Cells(2, 11).Value
k1 = .Cells(3, 11).Value
l = .Cells(2, 12).Value
l1 = .Cells(3, 12).Value
m = .Cells(2, 13).Value
m1 = .Cells(3, 13).Value
n = .Cells(2, 14).Value
n1 = .Cells(3, 14).Value
o = .Cells(2, 15).Value
o1 = .Cells(3, 15).Value
p = .Cells(2, 16).Value
p1 = .Cells(3, 16).Value
q = .Cells(2, 17).Value
q1 = .Cells(3, 17).Value
r = .Cells(2, 18).Value
r1 = .Cells(3, 18).Value
s = .Cells(2, 19).Value
s1 = .Cells(3, 19).Value
t = .Cells(2, 20).Value
t1 = .Cells(3, 20).Value
y1 = .Cells(2, 25).Value
y2 = .Cells(3, 25).Value
xinitial = .Cells(14, 2).Value
xfinal = .Cells(15, 2).Value
dx = .Cells(18, 2).Value
x = xinitial
n = 21

Do Until x = xfinal
If (Abs(a2(x) - b2(x))) <= 0.01 Then
.Cells(n, 1).Value = x
n = n + 1
x = x + dx
Else
x = x + dx
End If
Loop
End With
End Sub
Function a2(x As Variant) As Variant
a2 = -y1 + (a * (b * x ^ c + d * x ^ e + f * x ^ g + h * x ^ i) ^ j) / (k * (l * x ^ m + n * x ^ o + p * x ^ q + r * x ^ s) ^ t)
End Function
Function b2(x As Variant) As Variant
b2 = -y2 + (a1 * (b1 * x ^ c1 + d1 * x ^ e1 + f1 * x ^ g1 + h1 * x ^ i1) ^ j1) / (k1 * (l1 * x ^ m1 + n1 * x ^ o1 + p1 * x ^ q1 + r1 * x ^ s1) ^ t1)
End Function

 

[bjurney]

Overflow means that there are not enough system resources to run your function.


You can either make it so it doesnt loop as much, upgrade your memory on your computer, or perhaps close some other programs to free system resources. I believe you are allowed a limit of 32,767 loops before it overflows. What are you trying to do? Your code does nothing for me.

 

[AlphaFrog]

One thing I notice is the variables you define in eqn() are not passed to each of the two functions.

When you simply Dim the variables as you did within one procedure, they are available to only the procedure they are created in. Other procedures wouldn't recognize them.

You could Dim them outside the procedure or use the Public keyword eg:

[COLOR="Red"]Dim MyVar as Variant[/COLOR]

Sub eqn()
MyVar = [I]something[/I]
'''
End Sub

Function a2(x As Variant) As Variant
' MyVar is recognized
End Function

Function b2(x As Variant) As Variant
' MyVar is recognized
End Function

In the example above, MyVar is available to all procedures in the Module
Excel VBA Variables Scope and Lifetime


Instead of doing all this with VBA, you probably could just use the Solver tool.
Introduction to optimization with the Excel Solver tool

 

[danielrdgz10]

Thanks for the responses. The purpose in this program is to print the x solutions for the two functions at the buttom. I equaled both functions to A and B so in the if statement when A and B are almost identical it will print the value of x. This is my purpose and I dont know if this is the correct way. Also, I already Dim the variables outside the sub and it seems that the overlflow error got fixed; however, i am not getting the correct answers :s.