Need a non-compute intensive delay function for VBA 1

[cthonianmessiah]

I'm using API calls (SendInput mostly) to automate an external program through VBA, but the external program runs very slowly under the macro, and the problem seems to be that the macro is hogging all of the system resources even when waiting for a response.

The data is transmitted between the macro and the external app using the clipboard, so I actually have to check the clipboard periodically to determine if I am ready to continue.

I have a delay function that loops for a specified period of time to allow the external program to catch up, but it looks like continuous looping is using almost all of the available resources which I'd rather were allocated to the external program.

How can I use VBA to wait for a specified period of time without using excessive system resources? (My current compute-hogging delay function is included below.)

Private Sub delay(msec As Long)
    Dim inittime As Long
    inittime = CLng(Timer * 100)
    Do While CLng(Timer * 100) < inittime + msec
    Loop
End Sub

 

[bigtimmin]

You can use SLEEP

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Maybe this world is another planet’s Hell.
Aldous Huxley

 

[cthonianmessiah]

I knew this had to exist somewhere. Doesn't seem to fix the problem, but one aspect of it at least shouldn't be a problem anymore.

 

[bigtimmin]

<Doesn't seem to fix the problem.
I know the loop that you had would have cause your program to consume resource, but the Sleep doesn't. What problem remain?

Maybe this world is another planet’s Hell.
Aldous Huxley

 

[shakespeare5677]

You can always loop the sleep function until the external clipboard has the data. Also, depending on how you made the program, you can have the macro return a value to the external program (I know you can do that with excel, haven't tried others though).
-Max

 

[kencat742]

Will the sleep function work in a loop? I've tried it before and it just froze my computer completely. Instead I found a Wait command which i had loop every half second to check for a value to be true.

You've heard about the computer programmer that died while washing his hair in the shower. The instructions said, 'Lather, rinse, repeat.'

 

[shakespeare5677]

Sounds like an even better idea since it involves no crashing. I'd go with it.

 

[kencat742]

Here is the code I had found somewhere online, put it into a module of your program and to call it the line is 'Wait 1000' for one second, 'Wait 500' for half a second etc. hope it works for you

Option Explicit

Private Type FILETIME
    dwLowDateTime As Long
    dwHighDateTime As Long
End Type

Private Const WAIT_ABANDONED& = &H80&
Private Const WAIT_ABANDONED_0& = &H80&
Private Const WAIT_FAILED& = -1&
Private Const WAIT_IO_COMPLETION& = &HC0&
Private Const WAIT_OBJECT_0& = 0
Private Const WAIT_OBJECT_1& = 1
Private Const WAIT_TIMEOUT& = &H102&

Private Const INFINITE = &HFFFF
Private Const ERROR_ALREADY_EXISTS = 183&

Private Const QS_HOTKEY& = &H80
Private Const QS_KEY& = &H1
Private Const QS_MOUSEBUTTON& = &H4
Private Const QS_MOUSEMOVE& = &H2
Private Const QS_PAINT& = &H20
Private Const QS_POSTMESSAGE& = &H8
Private Const QS_SENDMESSAGE& = &H40
Private Const QS_TIMER& = &H10
Private Const QS_MOUSE& = (QS_MOUSEMOVE _
                            Or QS_MOUSEBUTTON)
Private Const QS_INPUT& = (QS_MOUSE _
                            Or QS_KEY)
Private Const QS_ALLEVENTS& = (QS_INPUT _
                            Or QS_POSTMESSAGE _
                            Or QS_TIMER _
                            Or QS_PAINT _
                            Or QS_HOTKEY)
Private Const QS_ALLINPUT& = (QS_SENDMESSAGE _
                            Or QS_PAINT _
                            Or QS_TIMER _
                            Or QS_POSTMESSAGE _
                            Or QS_MOUSEBUTTON _
                            Or QS_MOUSEMOVE _
                            Or QS_HOTKEY _
                            Or QS_KEY)

Private Declare Function CreateWaitableTimer Lib "kernel32" _
    Alias "CreateWaitableTimerA" ( _
    ByVal lpSemaphoreAttributes As Long, _
    ByVal bManualReset As Long, _
    ByVal lpName As String) As Long

Private Declare Function OpenWaitableTimer Lib "kernel32" _
    Alias "OpenWaitableTimerA" ( _
    ByVal dwDesiredAccess As Long, _
    ByVal bInheritHandle As Long, _
    ByVal lpName As String) As Long

Private Declare Function SetWaitableTimer Lib "kernel32" ( _
    ByVal hTimer As Long, _
    lpDueTime As FILETIME, _
    ByVal lPeriod As Long, _
    ByVal pfnCompletionRoutine As Long, _
    ByVal lpArgToCompletionRoutine As Long, _
    ByVal fResume As Long) As Long

Private Declare Function CancelWaitableTimer Lib "kernel32" ( _
    ByVal hTimer As Long)

Private Declare Function CloseHandle Lib "kernel32" ( _
    ByVal hObject As Long) As Long

Private Declare Function WaitForSingleObject Lib "kernel32" ( _
    ByVal hHandle As Long, _
    ByVal dwMilliseconds As Long) As Long

Private Declare Function MsgWaitForMultipleObjects Lib "user32" ( _
    ByVal nCount As Long, _
    pHandles As Long, _
    ByVal fWaitAll As Long, _
    ByVal dwMilliseconds As Long, _
    ByVal dwWakeMask As Long) As Long

Public Sub Wait(lNumberOfMilliSeconds As Long)
    Dim ft As FILETIME
    Dim lBusy As Long
    Dim lRet As Long
    Dim dblDelay As Double
    Dim dblDelayLow As Double
    Dim dblUnits As Double
    Dim hTimer As Long

    hTimer = CreateWaitableTimer(0, True, App.EXEName & "Timer")

    If Err.LastDllError = ERROR_ALREADY_EXISTS Then
        ' If the timer already exists, it does not hurt to open it
        ' as long as the person who is trying to open it has the
        ' proper access rights.
    Else
        ft.dwLowDateTime = -1
        ft.dwHighDateTime = -1
        lRet = SetWaitableTimer(hTimer, ft, 0, 0, 0, 0)
    End If

    ' Convert the Units to nanoseconds.
    dblUnits = CDbl(&H10000) * CDbl(&H10000)
    'dblDelay = CDbl(lNumberOfSeconds) * 1000 * 10000
    dblDelay = CDbl(lNumberOfMilliSeconds) * 10000
    
    ' By setting the high/low time to a negative number, it tells
    ' the Wait (in SetWaitableTimer) to use an offset time as
    ' opposed to a hardcoded time. If it were positive, it would
    ' try to convert the value to GMT.
    ft.dwHighDateTime = -CLng(dblDelay / dblUnits) - 1
    dblDelayLow = -dblUnits * (dblDelay / dblUnits - _
        Fix(dblDelay / dblUnits))

    If dblDelayLow < CDbl(&H80000000) Then
        ' &H80000000 is MAX_LONG, so you are just making sure
        ' that you don't overflow when you try to stick it into
        ' the FILETIME structure.
        dblDelayLow = dblUnits + dblDelayLow
        ft.dwHighDateTime = ft.dwHighDateTime + 1
    End If

    ft.dwLowDateTime = CLng(dblDelayLow)
    lRet = SetWaitableTimer(hTimer, ft, 0, 0, 0, False)

    Do
        ' QS_ALLINPUT means that MsgWaitForMultipleObjects will
        ' return every time the thread in which it is running gets
        ' a message. If you wanted to handle messages in here you could,
        ' but by calling Doevents you are letting DefWindowProc
        ' do its normal windows message handling---Like DDE, etc.
        lBusy = MsgWaitForMultipleObjects(1, hTimer, False, _
            INFINITE, QS_ALLINPUT&)
        DoEvents
    Loop Until lBusy = WAIT_OBJECT_0

    ' Close the handles when you are done with them.
    CloseHandle hTimer

End Sub

You've heard about the computer programmer that died while washing his hair in the shower. The instructions said, 'Lather, rinse, repeat.'

 

[Hypetia]

If all you need is to react to a change in clipboard contents, you should consider registering your application window as clipboard viewer window.

Windows sends notifications to windows in clipboard viewer chain when contents of the clipboard are modified... by any application on the system.

See thread222-576513 for more details. Instead of looping and polling clipboard continuously, your program will just sit idle and Windows will inform it automatically when clipboard is modified. Polling the clipboard continuously is also a resource hog in my opinion.

In the thread I mentioned above, you should place your code in 'ClipboardChanged' procedure, which is called when clipboard contents are changed.

Hope it helps.