pause vba action without using Application.Wait?

[[email protected]]

I have have a VBA program that uses a DDE link to refresh data values.
It takes about 10 seconds for the data to completely fill in. Once it
fills in, I use copy, then paste values to store the new values, and
then contnue processing the data through more vba code.


The problem is that I need to wait about 10 seconds for the data to
fill in with the DDE link. The VBA helpfile recommends using this code

to wait 10 seconds:


newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime


but the problem is that it pauses all Excel processing for those 10
seconds (not just the VBA code processing but all Excel) and the DDE
links don't update during the time. Is there a way I can pause my VBA
code, keep the Excel application processing during that time and then
resume my VBA code?

[Jake Marx]

Hi Mike,

Yes - you can use the OnTime method:

Sub test()
Debug.Print "test"
Application.OnTime Now + TimeSerial(0, 0, 10), "test2"
End Sub

Sub test2()
Debug.Print "test2"
End Sub

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

[email protected] wrote:
> I have have a VBA program that uses a DDE link to refresh data values.
> It takes about 10 seconds for the data to completely fill in. Once it
> fills in, I use copy, then paste values to store the new values, and
> then contnue processing the data through more vba code.
>
>
> The problem is that I need to wait about 10 seconds for the data to
> fill in with the DDE link. The VBA helpfile recommends using this
> code
>
> to wait 10 seconds:
>
>
> newHour = Hour(Now())
> newMinute = Minute(Now())
> newSecond = Second(Now()) + 10
> waitTime = TimeSerial(newHour, newMinute, newSecond)
> Application.Wait waitTime
>
>
> but the problem is that it pauses all Excel processing for those 10
> seconds (not just the VBA code processing but all Excel) and the DDE
> links don't update during the time. Is there a way I can pause my VBA
> code, keep the Excel application processing during that time and then
> resume my VBA code?

[[email protected]]

Thanks, I'm almost there. I'm just unable to figure out the proper
syntax for passing variables with Ontime

This works fine
Application.OnTime waitTime, "tasCopyPaste"

But if I try to pass variables I get an error:
Application.OnTime waitTime, "tasCopyPaste(Row, EndRow)"