Sunday, March 02, 2008

Sleep Function in Excel VBA

Application.Wait as Sleep in VBA

You can use Application.Wait instead of sleep function to hold the process for a specified period of time.

Here is the way to achieve that:

Sub Setting_Sleep_Without_Sleep_Function()

Debug.Print Now

Application.Wait DateAdd("s", 10, Now)

Debug.Print Now



End Sub

The code will give the following output

02-03-2008 19:12:47
02-03-2008 19:12:57

If you still require the Sleep Method here is it for you:



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

Here is a classical example of the use of Sleep function in a splash screen

Private Sub Form_Activate()


frmSplash.Show
DoEvents
Sleep 1000
Unload Me
frmProfiles.Show

End Sub

2 comments:

  1. Anonymous6:20 PM

    The problem is this locks Excel. For QueryTables this means data cannot be returned to Destination as at least 2 seconds release time is required - and DoEvents is too quick.

    ReplyDelete
  2. create a 1000x loop with doevents with a sleep 1, that will that way the doevents will not be so quick.

    ReplyDelete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...

Visual Basic for Applications (VBA) Forum (recent threads)

CodeKeep VBA Feed

Visual Studio Tools for Office Forum (recent threads)

Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.

Office Business Applications (OBA) Team Blog

MSDN Code Gallery Published Resources For Tag VSTO

microsoft.public.vsnet.vstools.office Google Group