Expression too complex

[Jethro25]

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!

[QWERTY]

How about something like this:

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.

[Jethro25]

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.

[Mongo]

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.

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

[Jethro25]

It works!

Thanks, Mongo. You da man.