Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pause to Calc
I've got vba code that copies formulas into a range, calculates, then
copy/paste-values. Since there are lots of formulas, the calculation takes 10 or 15 seconds, during which time the cursor shows an hourglass. If the user just waits during this time, it works fine. But if the user starts clicking around, the calculation doesn't work, and the code finishes with bad values. It's as if the code changes the formulas to values before the calculation is complete. How do I make it so that the calculation is complete, regardless of what the user does? One thing I'm thinking of is to have a pop up message of "Please be patient..." or whatever to appear while the calculation is going on. Would this help? Though I'm a skilled user of vba, I have never done this specifically (having a pop up appear while code runs in the background) and don't know how to do it. Many thanks in advance. Stuart http://home.comcast.net/~sresnick2/mypage.htm |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pause to Calc
Have a look here.
http://j-walk.com/ss/excel/tips/tip34.htm Should be right up your alley for what you are after. Corey.... wrote in message ups.com... I've got vba code that copies formulas into a range, calculates, then copy/paste-values. Since there are lots of formulas, the calculation takes 10 or 15 seconds, during which time the cursor shows an hourglass. If the user just waits during this time, it works fine. But if the user starts clicking around, the calculation doesn't work, and the code finishes with bad values. It's as if the code changes the formulas to values before the calculation is complete. How do I make it so that the calculation is complete, regardless of what the user does? One thing I'm thinking of is to have a pop up message of "Please be patient..." or whatever to appear while the calculation is going on. Would this help? Though I'm a skilled user of vba, I have never done this specifically (having a pop up appear while code runs in the background) and don't know how to do it. Many thanks in advance. Stuart http://home.comcast.net/~sresnick2/mypage.htm |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pause to Calc
Stuart,
May be sandwich your code in Application.Interactive=False/True statements. Make sure you use an error handling with Application.Interactive=True, just in case. NickHK wrote in message ups.com... I've got vba code that copies formulas into a range, calculates, then copy/paste-values. Since there are lots of formulas, the calculation takes 10 or 15 seconds, during which time the cursor shows an hourglass. If the user just waits during this time, it works fine. But if the user starts clicking around, the calculation doesn't work, and the code finishes with bad values. It's as if the code changes the formulas to values before the calculation is complete. How do I make it so that the calculation is complete, regardless of what the user does? One thing I'm thinking of is to have a pop up message of "Please be patient..." or whatever to appear while the calculation is going on. Would this help? Though I'm a skilled user of vba, I have never done this specifically (having a pop up appear while code runs in the background) and don't know how to do it. Many thanks in advance. Stuart http://home.comcast.net/~sresnick2/mypage.htm |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pause to Calc
wrote in message
ups.com... I've got vba code that copies formulas into a range, calculates, then copy/paste-values. Since there are lots of formulas, the calculation takes 10 or 15 seconds, during which time the cursor shows an hourglass. [snip] You should avoid calculation of formulas during execution of your code: Application.Calculation = xlCalculationManual (your code goes here) Application.Calculation = xlCalculationAutomatic Joerg |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pause to Calc
Many thanks to everyone who helped. The suggestion from NickHK below
worked fine. In normal Excel use, if you're in manual calc mode, and you hit the calc key, it will start to calc everything, but if you click on a cell during this time, the calc will stop before it's complete. This seems to be what happened with my code. When the code reached the "calculate" line, if the user happened to click, the code would continue with the next line without the calculation completing, resulting in bad results when the formulas get replaced by values. I sandwiched my "Calculate" line between the "application.interactive" statements, and now if the user clicks during the code execution, it's no problem. Stuart NickHK wrote: Stuart, May be sandwich your code in Application.Interactive=False/True statements. Make sure you use an error handling with Application.Interactive=True, just in case. NickHK wrote in message I've got vba code that copies formulas into a range, calculates, then copy/paste-values. Since there are lots of formulas, the calculation takes 10 or 15 seconds, during which time the cursor shows an hourglass. If the user just waits during this time, it works fine. But if the user starts clicking around, the calculation doesn't work, and the code finishes with bad values. It's as if the code changes the formulas to values before the calculation is complete. How do I make it so that the calculation is complete, regardless of what the user does? One thing I'm thinking of is to have a pop up message of "Please be patient..." or whatever to appear while the calculation is going on. Would this help? Though I'm a skilled user of vba, I have never done this specifically (having a pop up appear while code runs in the background) and don't know how to do it. Many thanks in advance. Stuart http://home.comcast.net/~sresnick2/mypage.htm |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calc = Manual & Do Not Calc b/4 SAVE | Excel Discussion (Misc queries) | |||
My calc key on Excel changes box to "Text" Box and doesn't calc ? | Charts and Charting in Excel | |||
Best way to pause? | Excel Programming | |||
auto calc on, but have to edit (f2) cells to force re-calc..help! | Excel Worksheet Functions | |||
Pause? | Excel Programming |