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
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.
ReplyDeletecreate a 1000x loop with doevents with a sleep 1, that will that way the doevents will not be so quick.
ReplyDelete