Implementing Sleep Function from VBA

Posted on 2005-04-07
Last Modified: 2008-02-01
I'm trying to implement the Sleep function from within a VBA module.  It compiles without error, but at run-time, the execution hangs the first time the subroutine gets called.

Here's my code:

Private Declare Function vSleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)
Const DelayTime = 20

Sub CommSleep(WaitMilliSec As Long)
    If WaitMilliSec > 0 Then
        Call vSleep(WaitMilliSec)
    End If
End Sub

... and to call the sleep function...

    Call CommSleep(DelayTime)

Any ideas what I'm doing wrong?

Question by:Mckenzma
    LVL 50

    Accepted Solution

    Call it as a Sub, ie

    Private Declare Sub vSleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)
    Const DelayTime = 20

    Sub CommSleep(WaitMilliSec As Long)
        If WaitMilliSec > 0 Then vSleep (WaitMilliSec)
    End Sub

    Sub start()
    Call CommSleep(DelayTime)
    End Sub

    LVL 8

    Expert Comment

    This does it in seconds. If using VBScript the Doevents will need to be commented out. In VBA(used in MS Office VBA) i think it will work.

    Const DelayTime = 20

    Call CommSleep DelayTime

    Sub CommSleep(WaitInSeconds)
              Dim Starter
          Dim Finisher
          Dim Eta
          Starter = Timer
          Do until Eta >= WaitInSec
                Finisher = Timer
                Eta = Finisher - Starter
          msgbox "Time is Up"
    End Sub

    Author Comment

    Dave, you're The Man.  Thanks!
    LVL 50

    Expert Comment

    by:Dave Brett
    Thanks for the grade

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Suggested Solutions

    Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
    Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now