Stgange overflow error

[alex009988]

I use this code

Function funcd1(p0 As Double, d1 As Integer, a As Integer, n As Integer, Y As Double)
funcd1 = Y - p0 * Y - ((d1 - 100) * p0 ^ (1 - n) * (a * p0 ^ n - 1) * Y / (100 * a * n))
End Function
Sub test()
Debug.Print funcd1(0.946098, 10, 20, 56, 1000000)
End Sub

And get Overglow error. it should equal 52181.8. But if

funcd1 = Y - p0 * Y - ((d1 - 100) * p0 ^ (1 - n) * (a * p0 ^ n - 1) * Y / (100 * a ))

it works and get the right output as -42429.4.
What's wrong with the first one when I add n at denominator.

[Paul_Hossler]

1. I'd make d1, a, and n Long but that did not cause the overflow

2. I figure Excel tried to calculate (100 * a * n) as an Integer since 100, a, and n were Integers

100 x 20 x 56 = 112,000 and that was the Integer overflow

You can

a. Make the 100 a floating point Double constant and it works (i.e. 100.0 which Excel displays a 100#)

or

b. Dim D1, a, and n as Long

or

c. Both a and b above

Function funcd1(p0 As Double, d1 As Long, a As Long, n As Long, Y As Double) As Double
    funcd1 = Y - p0 * Y - ((d1 - 100) * p0 ^ (1 - n) * (a * p0 ^ n - 1) * Y / (100# * a * n))
End Function

[alex009988]

Thank your for your reply. Now I know it. I have another strange moment. For exmaple.

Function func(x As Double)
func = x ^ 3 - x ^ 2 + 2
End Function
Function Dfunc(x As Double)
Dfunc = 3 * x ^ 2 - 2 * x
End Function
Function root(x As Double)
Dim eps As Double, h As Double
eps = 0.001
h = func(x) / Dfunc(x)
Debug.Print h & "**"
Do While Abs(h) >= eps
h = func(x) / Dfunc(x)
Debug.Print h
x = x - h
Debug.Print x & "*"
Loop
root = x
End Function
Sub main()
Debug.Print root(-20)
End Sub

But if I want to write h only inside the loop, it stops to work.

Function root(x As Double)
Dim eps As Double, h As Double
eps = 0.001
'h = func(x) / Dfunc(x)
Debug.Print h & "**"
Do While Abs(h) >= eps
h = func(x) / Dfunc(x)
Debug.Print h
x = x - h
Debug.Print x & "*"
Loop
root = x
End Function

Debug.print shows that h value is the same before loop and at the begining of the loop, inside of the loop.
So I dont really undestand why it has me to write h for two times?
Regards,

[Paul_Hossler]

If I understand what the question is ...

In the second version of Function root()

h = 0 and eps = 0.001

so

Abs(h) >= eps is aways False, so the Do While is never executed

Capture.JPG