Hi,
I have googled this several times and still haven't found much information at all.
When doing certain commands in VBA it's necessary to explicitly check that the processing pertaining to that command has actually been done before allowing VBA to continue.
So, for example, if calculating formulae on a worksheet, you have to wait until Application.CalculationState = xlDone before continuing - if you don't then it's possible to extract erroneous data.
If removing or creating a folder using RmDir and MkDir I think you need to check it's been deleted / created due to potential network lag once the command has been issued before continuing with code that expects the command to have been executed.
(In contrast, oddly, VBA seems to wait for a Kill command to complete before continuing.)
It seems(?) that this requirement to explicitly test for completion may also apply to things like sorting a large amount of data on a worksheet, or assigning values to a large range from a large variant array?
Is this right? If so, is the property that I need to check Application.CalculationState?
Are there any other standard commands that, if not explicitly coded to wait for execution to complete, would result in a risk to returning incorrect data, or encountering an error?
Thanks in anticipation for your help.
John
DoEvents should be enough.
After you call a recalculation, check the calculation state before allowing the code to go to next stage:
Application.Calculate
If Not Application.CalculationState = xlDone Then DoEvents
You can use in some cases Application.Wait Now() + TimeSerial(0,0,5) , if you know precisely the time needed to complete.
It's hard to know a priori which VBA commands need to be waited for. While testing code, you may find errors after certain commands, and placing DoEvents between these commands and the ones that have the error will reduce the errors.
These situations are more common in later versions of Excel (2013 and especially 2016), probably because of faster computers and multi-threading.
Examples tend to be working with Excel objects, doing things that take a short but noticeable time when done manually:
Creating a workbook
Inserting a worksheet
Creating a chart
Converting a range to a Table
Working with Pivot Tables
Inserting complicated formulas, especially in a large range
