annoying Excel VBA error

DrBlow · 11-06-2009, 03:58 AM

I'm writing some number crunching code which makes repeated calculations of simple polynomials. If I write the poly out as a factored expression like this:

P = D * (X + D * (F(1) + D * (F(2) + D * (F(3) + D * F(4)))))

then the code stops and I get a #VALUE# error. All the variables are initialised as type Double and have non-zero values.

If I express the same code as a For-Next loop it works fine:

P = X
For idx = 1 to 4
P = P +F(idx)*D^idx
Next
P = P*D

this is annoying me as I can't see why it is failing!

venkat1926 · 11-21-2009, 10:18 PM

the formula for P gives me an error ("formula too complex") in my version excel 2002.
so I have restructured the formula into parts (with some sample data-change them)

one more point you have entered
afer "next"
p*D
this will give p value for only idx=4
I suspect ths is a typo Put this line before next

now run his experimental macro ( which contains some message boxes which can be deleted or converted to comments ultimately)

Code:

Sub test()

Dim p As Double, D As Double, F(1 To 4) As Double, x As Double
Dim idx As Integer, q As Double

D = 2
F(1) = 3
F(2) = 4
F(3) = 5
F(4) = 6
x = 7

'p = D * (x + D * (F(1) + D * (F(2) + D * (F(3) + D * F(4)))))
p = F(1) + D * (F(1) + F(2) + F(3))
p = D * p
p = x + p
p = D * p
MsgBox p
'P = X
For idx = 1 To 4
p = p + F(idx) * D ^ idx

p = p * D
MsgBox p
Next

End Sub

11-06-2009, 03:58 AM	#1 (permalink)
DrBlow Registered User Join Date: Nov 2009 Posts: 1 OS: WinXP SP2	annoying Excel VBA error I'm writing some number crunching code which makes repeated calculations of simple polynomials. If I write the poly out as a factored expression like this: P = D * (X + D * (F(1) + D * (F(2) + D * (F(3) + D * F(4))))) then the code stops and I get a #VALUE# error. All the variables are initialised as type Double and have non-zero values. If I express the same code as a For-Next loop it works fine: P = X For idx = 1 to 4 P = P +F(idx)D^idx Next P = PD this is annoying me as I can't see why it is failing!

11-21-2009, 10:18 PM	#2 (permalink)
venkat1926 Registered User Join Date: Sep 2009 Posts: 13 OS: windows xp	Re: annoying Excel VBA error the formula for P gives me an error ("formula too complex") in my version excel 2002. so I have restructured the formula into parts (with some sample data-change them) one more point you have entered afer "next" pD this will give p value for only idx=4 I suspect ths is a typo Put this line before next now run his experimental macro ( which contains some message boxes which can be deleted or converted to comments ultimately) Code: Sub test() Dim p As Double, D As Double, F(1 To 4) As Double, x As Double Dim idx As Integer, q As Double D = 2 F(1) = 3 F(2) = 4 F(3) = 5 F(4) = 6 x = 7 'p = D (x + D * (F(1) + D * (F(2) + D * (F(3) + D * F(4))))) p = F(1) + D * (F(1) + F(2) + F(3)) p = D * p p = x + p p = D * p MsgBox p 'P = X For idx = 1 To 4 p = p + F(idx) * D ^ idx p = p * D MsgBox p Next End Sub