VB Circular Calculation

[Guest]

Hi.

I have a workbook which has circular calculations developed in Excel 2000.
I have included the following statement so that the workbook will calculate
circular formula reference.
With Application
.MaxIterations = 100
.MaxChange = 0.001
End With

Now that I am using Excel 2003, Excel pop-up reminder that Excel can not
calculate a formula which is circular. This only happens when another
workbook has been opened earlier.

I included the above code when the workbook opens. The Excel Pop-up
reminder does not show if I opened the subject workbook first.
With application
.Iterations = True
End With

I closed the iteration when I close the workbook.

With application
.Iterations = False End With
End With

I still have the same problem. What am I missing?

Is there a way or coding that I could use to supress this pop-up reminder no
matter when I open the subject workbook (i.e before or after another workbook
is opened)?

Any suggestions is greatly appreciated

Thanks.

Ligaya

 

[Simon Murphy]

Ligaya
application.displayalerts =false will stop all warnings, you could maybe
turn that on and off?
You might do better to remove the circularity by using a bit of VBA to break
the circle. just replace one/some(?) of the formulas with a value and calc
that value via VBA, maybe using a button or event?

cheers
Simon

Blog: www.smurfonspreadsheets.net
Website: www.codematic.net Excel development, support and training

 

[Guest]

Simon,

Thanks. I tried that approach, sometimes it works, sometimes not. I placed
the command when I open the workbook.

I probalby have to experiment where the command best fit.

Ligaya

 

[Guest]

Simon,

I tried placing the ApplicationDisplayAlert = false with no success.

The alert only comes up when no other workbook is open since I programmed
the calculations within the workbook for iteration. When another workbook is
open and iteration is not clicked on the opened workbook, my workbook
displays the circular reference alert.

I do not know where else to place the displayAlert = false.


Any other insigth is greatly appreciated.

Thanks

Ligaya

 

[Carl Hartness]

Workbook_Open and Workbook_BeforeClose may not cover all the
situations.

Have you tried putting your Iteration code into Workbook_Activate and
Workbook_Deactivate events in ThisWorkbook?

Workbook_Activate also runs after Workbook_Open, and
Workbook_Deactivate runs after Workbook_BeforeClose, so those
situations are covered as well.

Carl.

 

[Guest]

Carl,

Thank you. It worked like a charm.

I added the Workbook_Activate and Deactivate at the end to reset the alerts.
I probably did not have to do that because EXcel seems to reset everything
after running a macro.

Ligaya