[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Expression too complex error

Posted on 2010-09-02
15
Medium Priority
?
1,105 Views
Last Modified: 2012-05-10
Hi,

I am working on a routine to plot well dimensioned and well positioned circles on a X-Y chart, in another question.

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26426785.html

In the latest version, the question asker suddenly gets a "Expression too complex" error on the line

xScalar = DeltaX / mPA.InsideWidth

A very simple division of 2 Double variables, all three terms of this expression are double.

The error pops up in an iteration loop, limited by hardcoding to 100 iterations.

As I can't reproduce the error, and as the workarounds for this error seem to bring no help (simplifying the expression is not possible and I added a refresh as suggested in a MS article), I'm lost.

This is wat a Microsoft KB article suggested, but this did not help.

    Application.ScreenUpdating = True
    mCh.Chart.Refresh
    DoEvents


Thanks for your insights
ChartMAgic--version-3-.xls
0
Comment
Question by:calacuccia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
  • 3
15 Comments
 
LVL 3

Expert Comment

by:WiB
ID: 33596283

I would remove xScalar <> yScalar from the while loop, because expression is always true
restricting number of iterations doesn't help you neither (error occurs at first iteration)

during execution
xScalar = DeltaX / mPA.InsideWidth

one of the values(DeltaX) is calculated as a finite number, resulting to buffer overflow (as I saw under debugger)

and I didn't get your error in MS Office 2010



0
 
LVL 3

Expert Comment

by:WiB
ID: 33596325
and in 32-bit system only 6 nested levels supported in VB
0
 
LVL 46

Expert Comment

by:aikimark
ID: 33597742
@WiB

>>only 6 nested levels supported in VB
Please cite a reference for this comment or explain in more detail.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 3

Expert Comment

by:WiB
ID: 33598691
0
 
LVL 3

Expert Comment

by:WiB
ID: 33598794
apparently explicit casting do the trick:

xScalar = CDbl(DeltaX / mPA.InsideWidth)
0
 
LVL 17

Author Comment

by:calacuccia
ID: 33599173
I'll ask the person with the problem to test it :-)

Between the moment that I posted this question and your first replies, the person actually found a solution by doing this:

 xScalar = DeltaX / Round(mPA.InsideWidth, 20)

But I am still stunned by this simple equation raising the error.

0
 
LVL 46

Expert Comment

by:aikimark
ID: 33599278
@WiB

* The article states and 8 level (max) depth limit, not 6.

* The limit is on expression depth with floating point variables and literals -- how many parenthetical sub-expressions are there.

* This limit does not come into play for looping structures.
0
 
LVL 3

Expert Comment

by:WiB
ID: 33599389
that solution by rounding to 20 digits is even more strange to me than equation raising the error,
as Excel representation of double type has only 15 significant figures

I would say it is at least a flow in Excel code

P.S.: as you found workaround before the answer, you may delete the question
0
 
LVL 46

Expert Comment

by:aikimark
ID: 33599407
Rather than delete the question, please accept your comment http:#33599173 as the solution.
0
 
LVL 3

Expert Comment

by:WiB
ID: 33599415
aikimark, OK
but since error description is not correct in this case, don't see the point in further discussion
0
 
LVL 46

Expert Comment

by:aikimark
ID: 33599452
>>...further discussion

My last remark was directed to calacuccia, as the correct disposition for this question.
0
 
LVL 3

Expert Comment

by:WiB
ID: 33599650
:)
I actually was commenting your message about limit of levels of nested floating-point expressions
0
 
LVL 17

Author Comment

by:calacuccia
ID: 33600720
I was going to delete the question until you people started working on it :-)

WIB --> If you document this well, you'll get the points :-)

apparently explicit casting do the trick:

xScalar = CDbl(DeltaX / mPA.InsideWidth)

I would remove xScalar <> yScalar from the while loop, because expression is always true
restricting number of iterations doesn't help you neither (error occurs at first iteration)

Indeed, this condition was a leftover from a first trial, afterwards I added the t > 0.002 condition for better convergence. However, why do you state the error occurs after the first iteration? Have you been able to raise the error?
 
0
 
LVL 3

Accepted Solution

by:
WiB earned 2000 total points
ID: 33611071
>Indeed, this condition was a leftover from a first trial, afterwards I added the t > 0.002 condition for better
>convergence. However, why do you state the error occurs after the first iteration? Have you been able
>to raise the error?

yes, I got the error on the machine with MS Office 2007 (both Windows XP & Windows Vista)
variables:
DeltaX = 50
mPA.InsideWidth = 570,040078740157
i = 0
xScalar = -1,#IND


I guess this happened because one of the problems of floating-point computation in a computer is that

"operation can be legal in principle, but the result can be impossible to represent in the specified format, because the exponent is too large or too small to encode in the exponent field. Such an event is called an overflow (exponent too large), underflow (exponent too small) or denormalization (precision loss)."

explicit casting normalizes indefinite result


0
 
LVL 17

Author Closing Comment

by:calacuccia
ID: 33611147
Well, thanks for the addition, let's settle it here :-)
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question