OWC and circular references

[Raj]

Hi,
I am using OWC spreadsheet 11.0 control in C#.NET 2.0 winforms. I have a
spreadsheet with formulas having circular references (The circular reference
is mandatory due to business requirement).

Please note that the iteration for circular references has been set to a
limit (100). (FYI - Go to Tools->Options->Calculation in excel). The formula
does work correctly when opened directly in excel.

However, when this spreadsheet is loaded in OWC control placed on a winform,
the circular references does not work. It displays CIRC! wherever circular
reference is found.

Problem:
Business mandates the use of circular reference.
1. Is there any workaround to make the circular reference work in OWC control?

OR

2. Is there any alternative to load excel document in winforms?. I explored
2 other options which does not work quite well.
a. dsoFramer - There is no support for this in Microsoft.
b.webbrowser control - If I load excel document in the control, circular
reference does work but could not manipulate the worksheets, cells within the
excel document. Also, standard excel menu options does not show up.

Please pour your suggestions and thoughts. This is really critical because
the whole project's architecture is dependant on OWC control.

 

[Alvin Bruney [ASP.NET MVP]]

I know of no workaround for this.

--

Regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
The O.W.C. Black Book, 2nd Edition
Exclusively on www.lulu.com/owc $19.99

 

[Erik Eckhardt]

It sounds like you want to do an iterative calculation. You can work around
this with some custom functions or event handling.

Basically what you want is for the result of each calculation to be the
input of the next calculation. Each of your 100 iteration's results gets more
and more accurate, as you do some kind of approximation (or limit approach)
in your formulas to zero in on your final result because of some
hard-to-predict or difficult-to-compute slopes or something.

So try this:

1. Break the circular reference by putting your final output values into
another range
than the input values.
2. Programatically, copy the output range values to the input range values
and calculate. Do this 99 times.

You'll need to populate the starting range values (perhaps zeroes) and
you'll need some way to initiate your code that copies the values over and
starts another calculation.

I have used this method myself several times (but not in OWC). In regular
Excel, where you can have VBA code attached, I have created custom functions
which behind-the-scenes make updates to other cell values. This defeats
Excel's circular reference detection because it doesn't know the function has
value-changing side-effects. Then all I had to do was hit F9 as many times as
I wanted.

You could also implement it as a simple loop from the start,
programmatically kicking off the calculation as many times as you want or
until the precision of your values is within the desired tolerances.