Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Circular Calculation
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Circular Calculation
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 "Ligaya" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Circular Calculation
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 "Ligaya" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Circular Calculation
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 "Simon Murphy" wrote: 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 "Ligaya" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Circular Calculation
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. On Mar 7, 9:10 am, Ligaya wrote: 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 "Ligaya" wrote: 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- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Circular Calculation
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 "Carl Hartness" wrote: 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. On Mar 7, 9:10 am, Ligaya wrote: 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 "Ligaya" wrote: 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- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Circular or semi-circular chart | Charts and Charting in Excel | |||
circular reference calculation error | Excel Worksheet Functions | |||
Need help with Circular (?) | Excel Worksheet Functions | |||
How can I enable circular calculation permanently? | Setting up and Configuration of Excel | |||
range.calculation with UDF not working when calculation is set to automatic | Excel Programming |