77836

Excel VBA wait for Application.calculatefull

Question:

when I call Application.calculatefull and put a breakpoint at the next statement, I see in the status bar that processor processing after a while but has already reached the next line of code.

I checked the forum and people suggested to use DoEvents. In my case its a problem. As the worksheet has many formula and some of them show wrong result after first run though formula is correct, so when I call the calculateFull for the worksheet, it corrects it but doesn't wait

If I use DOEvents, the CalculateFull doesn't update the worksheet.

So I am in a fix.

Please advice how can I wait for the processor to complete processing on application.CalculationFull and after that only go to next line of code.

I used application.wait also but it seems to hold the processor and once released it starts calculation then, which doesn't solve my problem.

<hr />waittime (1000) SendKeys "+^%{F9}", True Application.CalculateFullRebuild

This is the code I use for now. When I put the breakpoint at the next line of code, I see that the application.calculatefullRebuild takes a few secs and goes the next line and when I check the activesheet now, in another 2-3 sec I see in status bar Calculating (4 processors):xx% and after which the worksheet values are changed to the right values

This is my issue. When Application.CalculateFullRebuild completes and goes to the next line, my worksheet results are still not accurate and only after a couple of secs the processors calculating statusbar message, the worksheet is updated.

How can I wait for this to complete, otherwise the remaining code will pick up wrong results.

Answer1:

So I believe that the question <a href="https://stackoverflow.com/questions/11277034/wait-until-application-calculate-has-finished" rel="nofollow">here</a> has the answer you are looking for (modified slightly below to put it in a while loop):

Do While Application.CalculationState <> xlDone DoEvents Loop

Sticking the above loop in your code right after starting the calculation process should be all that is required.

Answer2:

I used the same solution as <a href="https://stackoverflow.com/users/1866809/brad" rel="nofollow">Brad</a> extended by a timer to inform the user if things go wrong. I found that without triggering the Application.Calculate command (or CalculateFull) excel did not proceed calculating by the DoEvents allone.

Const MAXTIME_S = 10 Dim t As Double t = Timer() If Application.CalculationState <> xlDone Then Application.Calculate Do While Application.CalculationState <> xlDone DoEvents If Timer() - t > MAXTIME_S Then Exit Do Loop If Application.CalculationState <> xlDone Then MsgBox "Calculation not done. Please restart this Macro.", vbInformation + vbOKOnly, "Aktualisieren" Exit Sub End If

Recommend

  • Properly disposing of a DbConnection
  • Uncaught DOMException: Failed to read the 'rules' property from 'CSSStyleSheet'
  • How to get log information in android
  • DirectoryChooser Javafx buged?
  • UINavigationController alternatives
  • Anyone got LunarEclipse to work with Eclipse 3.4.1?
  • Reference cursor gets lost in XMLType.createxml
  • App not appearing in iCloud “Manage Storage” list
  • Firefox version for selenium-firefox-driver 2.41.0
  • Spring Security 3.1.3 @EnableWebSecurity
  • HDFS resiliency to machine restarts in DC/OS
  • Is it OK to write code after [super dealloc]?
  • iAd works in iPhone but not iPad
  • rselenium | get youtube page source
  • How to show underscore (shortcut) without holding Alt?
  • data.table replicate rows after join?
  • HALF_PTR Windows data type
  • Can XOR be expressed using SKI combinators?
  • F#: In which memory area is the continuation stored: stack or heap?
  • change search magnifying glass to UIActivityIndicatorView
  • Most efficient way to move table rows from one table to another
  • Magento get URL before current
  • Prevent Tomcat from caching request during starup
  • C# fibonacci function returning errors
  • How do I signal completion of my dataflow?
  • Tamper-proof configuration files in .NET?
  • How to use JavaScript to determine whether a file exists in a directory?
  • why xml file does not aligned properly after append the string in beginning and end of the file usin
  • Python CGI os.system causing malformed header
  • Spring Data JPA custom method causing PropertyReferenceException
  • C# - Serializing and deserializing static member
  • Date difference with leap year
  • Is possible to count alias result on mysql
  • Sending data from AppleScript to FileMaker records
  • Hazelcast - OperationTimeoutException
  • Jquery - Jquery Wysiwyg return html as a string
  • Confusion with PayPal's monthly billing cycle
  • How to include full .NET prerequisite for Wix Burn installer
  • Free memory of cv::Mat loaded using FileStorage API
  • Is it possible to post an object from jquery to bottle.py?