Pausing or waiting in VBA

[Brad]

I come to a point in my VBA code where an array formula is calculated and it
pulls data from a DDE link, so the calculation is not always instantaneous
as it takes a few seconds to retrieve the data. In the meantime the program
continues to step forward and I am getting an error message at a point where
I am trying to assign a value to a variable based on a cell that is
dependent on the aforementioned calculation of the array formula.

I first tried just using a message box to freeze the program long enough for
the array formula to compute, but it froze it froze the array formula too,
so no help there. So, I sort of feel like a dog chasing its tail here. I am
not certain I know what I need but some sort of code after this:

Selection.FormulaArray = MySecondFormula

where the program pauses until the array formula has pulled its data in from
the DDE source and calculated the cell values, then as the code continues to
step forward, there would be a value where and when I need it to pass along
to the variable. I only get an error now.

My thanks in advance for any suggestions.

Cheers! Brad

 

[Gary''s Student]

Don't wait. Just leave the macro and rely on an Application.OnData event to
trip when the transfer is complete.



--Gary's Student

 

[Jim Cone]

Brad,
The following allows ten seconds to retrieve the
MySecondFormula value or it displays a message and
continues on. It assumes the first cell in the selection is blank.
You could alter this to provide alternatives...

Dim TimeNow As Date
TimeNow = Timer
Selection.FormulaArray = MySecondFormula
Do
If TimeNow + 10 < Timer Then
MsgBox "Didn't Work "
Exit Do
End If
Loop Until Len(Selection(1).Value) > 1
------------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Brad" <[email protected]>
wrote in message
I come to a point in my VBA code where an array formula is calculated and it
pulls data from a DDE link, so the calculation is not always instantaneous
as it takes a few seconds to retrieve the data. In the meantime the program
continues to step forward and I am getting an error message at a point where
I am trying to assign a value to a variable based on a cell that is
dependent on the aforementioned calculation of the array formula.

I first tried just using a message box to freeze the program long enough for
the array formula to compute, but it froze it froze the array formula too,
so no help there. So, I sort of feel like a dog chasing its tail here. I am
not certain I know what I need but some sort of code after this:

Selection.FormulaArray = MySecondFormula

where the program pauses until the array formula has pulled its data in from
the DDE source and calculated the cell values, then as the code continues to
step forward, there would be a value where and when I need it to pass along
to the variable. I only get an error now.
My thanks in advance for any suggestions.
Cheers! Brad