ensuring that all macros/queries have completed

[JeffGrant]

Hi All,

I am looking for some suggestions. In my model I have a lot of macros and querries that generally need to run sequentially. I have a considerable number of application.wait and timer loops to ensure that all queries and macros are completed, at least to the best of my ability anyway.

Because the wait statements and timer loops generally use a fixed waiting time of some value, on the whole everything works fine.
However, occasionally, I get a hiccup where something has not completed in time which leads to run time errors down the line.
I am looking for suggestions on how best to "monitor" that everything gets completed before the actual processing starts.

I am considering writing out a flag somewhere and summing the flags so that if the sum of the flags does not equal an expected total, then I know that not all the macros have run.

Has anybody got any other ideas?

As usual, I thank you in advance for your input.

Jeff.

 

[myall_blues]

In a former (non-Excel) life, I used to do something similar to what you are suggesting, by writing out an 'audit trail' to a small text file. Each subsequent macro/process could then check that file to ensure it exists or contains an expected line. If yes carry on, if no wait a bit longer

In VBA pseudo-code it would look something like:
Sub Macro1()
Delete macro1audit.txt if it exists
'
' Do macro processing
'
open macro1audit.txt for input as #1
close #1
end sub

Sub Macro2()
Delete macro2audit.txt if it exists
open macro1audit.txt for input as #1
if #1 exists, carry on
close #1
'
' Do macro processing
'
open macro2audit.txt for input as #2
close #2
end sub

and so on.