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 |
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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 11:57 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com