-
I'm trying to create a function to be used in Excel, which calculates the death benefit for a member of a pension plan.
When I try calling the function from a subroutine procedure the debugger gives me the message "Expression too complex".
The following is the chunk of code that is giving me trouble:
*****************************
For i = 55 To 64
loopcalc2 = (lx(i, table_number) - lx(i + 1, table_number)) / lx(Age, table_number) * (loopcalc1 * AccBen * GLA(65 * 12, 60, table_number, interest_number, 1) + FiftyXS + HundredXS)
loopcalc3 = Ann(Age * 12, Age * 12 - 12, table_number, table_number, 1, 0.00000001, Age * 12, 180, 2, 180, interest_number, 1)
loopcalc4 = loopcalc2 * loopcalc3
loopcalc5 = loopcalc4 / GLA(i * 12, 180, table_number, interest_number, 1)
Post55Single = Post55Single + loopcalc5
Next
*****************************
As you can see, I've tried to break the expression up into smaller pieces, but VB still keeps stopping at the "loopcalc5..." line.
Note: lx, gla, & ann are functions from an add-in to Excel and VB that my company uses.
Could somebody give me some advice here?
Thanks alot!
-
How about something like this:
Code:
Dim GLAValue As WHATEVERITIS
GLAValue = GLA(i * 12, 180, table_number, interest_number, 1)
loopcalc5 = loopcalc4 / GLAValue
It probably won't work, but you could give it a try.
-
Hi QWERTY,
I tried this, but this time the debugger stoped at the "GLAValue = ..." line with the same "Expression too complex" message.
Thanks for trying, QWERTY. I appreciate the effort.
-
You'll get a "too complex" error when a floating-point expression contains too many nested subexpressions. So, you were on the right track. Keep breaking your expression into as many separate expressions as necessary to prevent the error from occurring.
Code:
Dim i as Integer i12 As Integer, age1 As Integer, age2 As integer
Dim lx1 As Double, lx2 As Double, lx3 As Double
Dim gla1 As Double, gla2 As Double
For i = 55 To 64
lx1 = lx(i, table_number)
lx2 = lx(i + 1, table_number)
lx3 = lx(Age, table_number)
' 65 * 12 = 780
gla1 = GLA(780, 60, table_number, interest_number, 1)
loopcalc2 = (lx1 - lx2) / lx3
loopcalc2 = loopcalc2 * loopcalc1 * AccBen * gla1 + FiftyXS + HundredXS
age1 = Age * 12
age2 = age1 - 12
loopcalc3 = Ann(age1, age2, table_number, table_number, 1, 0.00000001, age1, 180, 2, 180, interest_number, 1)
loopcalc4 = loopcalc2 * loopcalc3
i12 = i * 12
gla2 = GLA(i12, 180, table_number, interest_number, 1)
loopcalc5 = loopcalc4 / gla2
Post55Single = Post55Single + loopcalc5
Next i
-
It works!
Thanks, Mongo. You da man.