Excel Not Finishing Calculation Before Moving to Next Line of Code

Hello everyone,

I have a very odd problem. I have found a few instances where other people had this problem, but I have yet to find a solution.

I have a macro-enabled workbook that performs several time consuming tasks, and it takes a fair amount of computing time (about 10 minutes, start to finish). Near the end of the macro, it opens several Excel files, all of which are linked to the file with the macro, and performs a "save as" function to save them as CSV files or tab delimited, depending on the end purpose for the file. I noticed the "saved as" files did not appear to have the correct data. So, I began to investigate and realized it was opening and saving the files before the calculations in the original worksheet were complete.

If I manually "step into" the macro and run it line by line, it works beautifully. However, if I just let it go, it will "outrun" itself by moving to the next line of code before the calculations from the previous step are complete. I am stumped. I have tried:

1. Wait commands
2. Do events Loops
3. CalculateFull
4. Manual and Automatic Calculations
5. Calculate the workbook
6. Calculate each sheet individually
7. I am sure more that I cannot remember.

Any ideas how to make the macro wait until the calculations are 100% complete before proceeding?

Jacc,

Thank you for the information. Unfortunately, that did not work. The only thing I can think of, is Excel completes one set of calculations, which triggers another set, and another set, and so on. Maybe it is catching those split seconds calculations are not running between iterations?

DBCox, I am having the same issue with one of my macros.
Did you find something?

I am so sorry it took me this long to reply. The email notification got buried in my inbox.

Someone suggested I try:
Application.CalculateUntilAsyncQueriesDone

It worked! Apparently, the external data was not completely updated before calculations began. This code prevents that.

Hope it helps!