You may want some way of pausing or delaying VBA code execution and you can do this with two functions called Wait and Sleep.
You can also do this using a loop, and we will look at that approach too, but first we’ll look at the functions available to Excel.
Why would you pause the code?
Maybe you need to wait for another task to finish, for instance if you made a call to a Windows API/shell function.
Or you may want to wait for the user to update data in the sheet, or you just want to run a macro at a set time.
Download the Workbook With Sample VBA Code
Enter your email address below to download the sample workbook.
Application.Wait
The .Wait method is available within Excel as a VBA function, as opposed to Sleep (see below). You can use it to specify that a macro is paused for a specific period of time.
This example makes the macro pause for approximately 10 seconds:
Application.Wait (Now + TimeValue("0:00:10"))
Or you can pause execution until a specific time e.g. this will pause a macro until 11am:
Application.Wait "11:00:00"
Wait does not accept delays of less than 1 second.
Sleep
Sleep is a Windows API function, that is, it is not part of VBA it is part of the Windows operating system.
But we can access it by using a special declaration statement in our VBA.
This declaration statement serves two purposes. Firstly, it tells Excel where to find the function, secondly it allows us to use the 32bit version of the function in 32bit Excel, and the 64bit version of the function in 64bit Excel.
The Declare statement looks like this
#If VBA7 Then ' Excel 2010 or later
Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal Milliseconds As LongPtr)
#Else ' Excel 2007 or earlier
Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal Milliseconds As Long)
#End If
You can read more about these type of Declare statements and 32bit/64bit Office on Microsoft's MSDN site
Sleep allows us to pause a macro for X milliseconds, which is a better resolution than Wait which has a minimum delay of 1 second.
So to pause a macro for 5 seconds using Sleep we write this
Sleep 5000
Loops
The big drawback of using Wait or Sleep, is that Excel locks you out until the wait/sleep period has finished.
You can use CTRL+BREAK to interrupt the macro, but Excel won’t accept input from the keyboard or mouse whilst paused using Wait or Sleep.
Events are suspended and anything you have scheduled using Application.OnTime is also delayed until the pause has finished.
If you didn’t know what was happening, it looks like Excel has hung whilst Sleep or Wait are in effect. Background processes like printing and recalculation do carry on though.
To overcome this drawback you can use a loop to pause VBA execution, and also allow other things to happen whilst waiting.
Macro execution isn’t actually paused, it’s just not doing anything other than running some loop commands.
A loop has an added advantage as Mac users can use them, whereas Wait and Sleep are not available on a Mac.
A simple loop would look something like this
Sub WasteTime(Finish As Long)
Dim NowTick As Long
Dim EndTick As Long
EndTick = GetTickCount + (Finish * 1000)
Do
NowTick = GetTickCount
DoEvents
Loop Until NowTick >= EndTick
End Sub
We write a sub called WasteTime, which when called from another sub or function has a value passed into it which is the number of seconds that we want it to do nothing, like so:
WasteTime(10)
The key here is the DoEvents method. DoEvents tells Excel to check if there is anything else the system wants to do like accept input from the keyboard or mouse.
In this example, my macro is executing but allows the user to type into the worksheet.
After approximately 10 seconds a message is then displayed.
The GetTickCount function is another Windows API function that we access by using another Declare statement.
It returns the number of milliseconds since the computer started up.
The parameter, Finish that we pass into the WasteTime sub, is the number of seconds we want to delay code execution. To convert this to milliseconds, we multiply Finish by 1000.
Timer Resolution
In my examples I have used the word approximately when describing the delays I’m trying to achieve.
I say approximately because the actual duration of the pause in the execution of the code depends on the resolution of the timer on your computer.
I could give you a very complicated description why this is so, or you could Google it, but let’s just say, if you want to delay something by 10s, then you will delay it for around 10s.
It’ll only be out by a few milliseconds, which is perfectly fine for the type of things I am doing. I just wouldn’t use it to time Usain Bolt.
Disclaimer – Please test the code yourself, it may not work in your environment. All code provided as is without any warranty
Excel Quick Analysis
Jim Frangos
I am interested in .NET code to open a local .XLSX file which is 32-bit.
JIM
Philip Treacy
Hi Jim,
Sorry we don’t do any .NET programming.
Regards
Phil
Deepak
I attached my vba record
It’s running perfectly for 10mins after that its slows down and takes 5sec to load and increasing time on process please fix this
sub Test()
Application.OnTime Now + TimeValue(“00:00:03”), “Test”
Call Macro1
End sub
Sub Macro1()
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.DisplayStatusBar = False
.EnableEvents = False
End With
Range(“A14:N14”).Insert Shift:=xlDown, copyOrigin:=xlFormatFromLeftOrAbove
Range(“A10”).Copy
Range(“A14”).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range(“L2:L7”).Copy
Range(“B14”).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Range(“K2:K7”).Copy
Range(“H14”).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Range(“C12”).FormulaR1C1 = “=R[-10]C[9]-R[13]C[-1]”
Range(“D12”).FormulaR1C1 = “=R[-9]C[8]-R[13]C[-1]”
Range(“E12”).FormulaR1C1 = “=R[-8]C[7]-R[13]C[-1]”
Range(“F12”).FormulaR1C1 = “=R[-7]C[6]-R[13]C[-1]”
Range(“G12”).FormulaR1C1 = “=R[-6]C[5]-R[13]C[-1]”
Range(“H12”).FormulaR1C1 = “=R[-5]C[4]-R[13]C[-1]”
Range(“I12”).FormulaR1C1 = “=R[-10]C[2]-R[13]C[-1]”
Range(“J12”).FormulaR1C1 = “=R[-9]C[1]-R[13]C[-1]”
Range(“K12”).FormulaR1C1 = “=R[-8]C-R[13]C[-1]”
Range(“L12”).FormulaR1C1 = “=R[-7]C[-1]-R[13]C[-1]”
Range(“M12”).FormulaR1C1 = “=R[-6]C[-2]-R[13]C[-1]”
Range(“N12”).FormulaR1C1 = “=R[-5]C[-3]-R[13]C[-1]”
Range(“H10”).Copy
Range(“N14”).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Range(“B14:M14”).Select
Range(“P10”).FormulaR1C1 = “=RC[-8]-R[5]C[-2]”
Range(“A26:N26”).Delete Shift:=xlUp
Range(“R2:Y2”).Insert Shift:=xlDown, copyOrigin:=xlFormatFromLeftOrAbove
Range(“A10,P10”).Copy
Range(“R2”).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range(“O2:O7”).Copy
Range(“T2”).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Range(“R10:Y10”).Delete Shift:=xlUp
Range(“A10”).Copy
Range(“Z1”).End(xlDown).Select
ActiveCell.Offset(1, 0).Range(“A1”).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range(“A10”).Copy
Range(“Z95”).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range(“C27:Q27”).Copy
Range(“AA95”).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range(“Z2:AO2”).Delete Shift:=xlUp
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.DisplayStatusBar = True
.EnableEvents = True
End With
Call Test
End Sub
Philip Treacy
Hi Deepak,
This line selects a cell on the last row
Range("Z1").End(xlDown).Selectthen this line generates an error as it tries to access a cell in the row below the last row – which doesn’t exist
ActiveCell.Offset(1, 0).Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=FalseRegards
Phil
sunil
I am a beginner, can you please help me with this code. I am not able to come out of the loop.
Sub WasteTime(Finish As Long)
Dim NowTick As LongLong
Dim EndTick As LongLong
EndTick = GetTickCount + (Finish * 1000)
Do
NowTick = GetTickCount
DoEvents
Loop Until NowTick >= EndTick
End Sub
Sub Paste_Values1()
Range(“B1”).Copy
Range(“B8”).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Range(“A8”) = Now
WasteTime (1)
Range(“B1”).Copy
Range(“B9”).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Range(“A9”) = Now
End Sub
Philip Treacy
Hi Sunil,
When I step through this code I get no issues.
Have you tried using the debugging tools in the VBA Editor to step through the code and see what it is doing? Press F8 start executing 1 line at a time.
Regards
Phil
Sunil
thanks for the reply Phil.
Actually, I want to store the value of the cell every 5 min interval and plot a chart using that data.
please help me with this.
Mynda Treacy
Hi Sunil, please post your question on our Excel forum where you can also upload a sample file and we can help you further.
Yuvraj
Hi ,
I want that my VBA code is run continue 29 days but after 29 days it will not run, men’s automatic stop after 29 days.
Catalin Bombea
Hi Yuvraj,
Set a start time:
Dim StartTime as Date
StartTime=Now()
Then, in your code that runs continuously, add an exit if the date exceeds starttime + 29:
If CLNG(Date)>CLNG(StartTime)+29 then Exit Sub
Max
Thanks so much for this post. Adding the declaration of GetTickTime at the top of the window would be the icing on the cake. Since it doesn’t throw an error when it is not declared it took me a while to figure out what is missing.
Public Declare PtrSafe Function GetTickCount Lib “kernel32.dll” () As Long
Philip Treacy
Hi Max,
Not sure exactly what you are meaning. You mention declaring GetTickTime but your code is declaring GetTickCount – which is already declared.
Regards
Phil
Boris Pretzel
Hi,
Thanks for the handy tips.
There seems however to be an error in the declare construct in the accompanying workbook.and as listed here (for the sleep and wastetime routines).
You have:
#if vba7 then
Public Declare PtrSafe Sub Sleep Lib “kernel32” (ByVal Milliseconds As LongPtr)
#Else
Public Declare PtrSafe Sub Sleep Lib “kernel32” (ByVal Milliseconds As Long)
#End If
BUT surely PtrSafe is only available in versions of excel after 2007 so the #Else statement is incorrect. (In the accompanying spreadsheet you also have LongPtr under the #Else statement)? So should the correct format not be:
…
#Else
Public Declare Sub Sleep Lib “kernel32” (ByVal Milliseconds As Long)
#End If
(and any LongPtr references changed to just Long for VBA<7)?
Of course, if you correct the format, the VBA editor (in 64 bit excel later that 2007) throws an error saying the the statements under the #Else part are not compatible with 64 bit Excel (they are not). I guess you just have to ignore the error?
Catalin Bombea
Yes, you can just ignore the error, the compiler will still raise that error.
Rik van Zomeren
Code not working anymore excel 2016 windows 10;
WasteTime(Finish As Long):
I’m getting compile error:
Type mismatch
Catalin Bombea
Hi Rik,
You have to change the type of the parameters to work in 64 bit system:
Sub WasteTime(Finish As Long)
#If VBA7 Then
Dim NowTick As LongPtr
Dim EndTick As LongPtr
#Else
Dim NowTick As Long
Dim EndTick As Long
#End If
Geoff Strickler
The delay loop using
Do
NowTick = GetTickCount
DoEvents
Loop Until NowTick >= EndTick
uses a lot of CPU cycles. Adding an ~10ms-100ms call to Windows Sleep function brings CPU utilization back to near 0 while remaining very responsive.
Dim DelayMS as long
DelayMS =50 ‘ can use a shorter sleep time, but this is quite responsive
Do
Sleep DelayMS ‘idle for a bit
NowTick = GetTickCount
DoEvents
Loop Until NowTick >= EndTick
Not certain if a similar kernel function call can be made in the Mac version. Suspect it can, but I don’t yet know how.
Catalin Bombea
Unfortunately, that function is a windows function only, there is no similar library in Mac.
Frederic Biarnes
Hello Phillip,
Thank you for this tutorial.
I downloaded your workbook with sample VBA code, but I got an xlsx file with no macros :<>
Do you know how I could get the original xlsm file ?
Regards,
Frederic
Philip Treacy
Hi Frederic,
The only file you can download on this page is an XLSM which contains macros. Not sure how you got an XLSX.
Please try downloading the file again.
Regards
Phil
Chip
Man, you just come short of doing what is needed… Now I have to go and find the declaration for GetTickCount. (sigh)
Well… thanks… anyway… how about adding that to this lesson. 😉
Philip Treacy
Chip,
Did you download and open the example workbook I provided in this blog post? It includes the declaration for GetTickCount.
Regards
Phil
Khan
I dont think the vba code for Loop is correct. There is no increment or decrement statement for NowTick variable in the WasteTime sub and is making the code to be in an infinite loop.
Philip Treacy
Hi Khan,
NowTick gets the ticks from the system clock so every time you call GetTickCount it’s a new value. No need for a separate increment statement. Your code shouldn’t loop infinitely.
Regards
Phil
AP
The countdown worked correctly for me after putting the following in the subroutine
#If VBA7 Then ‘ Excel 2010 or later
Dim NowTick As LongLong
Dim EndTick As LongLong
#Else ‘ Excel 2007 or earlier
Dim NowTick As Long
Dim EndTick As Long
#End If
Mike
The Sleep command from Kernel32 just crashes Powerpoint. Not sure why.
Also, in addition to calling “DoEvents”, make sure to also call “WaitMessage” otherwise none of your events will fire.
Philip Treacy
Hi Mike,
It works fine for me, though I’d never run it in PowerPoint before today. You may have a different environment to me.
Phil
Sophie Kernahan
Thanks for a great article.
When downloading your workbook, the WasteTime sub worked perfectly on my 64 bit Offce365 excel however, when embedding it into my own workbook, the routine doesn’t seem to stop after 10 seconds, or bring up the msgbox at all. When I look at the toolbar of the developer window, the code is still “running” long after 10 seconds until I “stop” it manually.
Is there any settings I need to ensure I have set in my workbook?
Many thanks.
Philip Treacy
Thanks Sophie.
Hard to say what’s happening without seeing your code and being no your PC to check it. Especially as it ran ok already.
Try changing the WasteTime sub to this
Sub WasteTime(Finish As LongPtr) Dim NowTick As LongPtr Dim EndTick As LongPtr EndTick = GetTickCount + (Finish * 1000) Do NowTick = GetTickCount DoEvents Loop Until NowTick >= EndTick Debug.Print "end" End SubIf you are still having issues please start a topic on the forum and attach your workbook.
Regards
Phil
Dan Pack
Is there a delay execution function of Excel cell Formulas? (ie: Col B Formulas waiting for 3 secs. for Col A Formulas to finish then Col B Formulas execute). Thx
Philip Treacy
Hi Dan,
No there’s isn’t such functionality. You could write some event code to implement something like this but why would you need it? What is happening in Col A? And once Col A formulae recalculate, won’t the formulae in Col B update too?
Regards
Phil
José Andrés
Thank you very much!
Philip Treacy
You’re welcome
vknowles
I found that the Sleep function worked in Outlook 2013 but caused Outlook to appear to freeze (forms went blank for the duration of the sleep time). For virtually no cost in CPU, I found that putting Sleep 100 in a loop with DoEvents and counting down 100 milliseconds per loop worked well with no visible change to the Outlook UI and no discernible CPU bump.
Philip Treacy
Thanks 🙂
Steve Rindsberg
Nice article! But the Sleep example here is incorrect. #IF VBA7 is true if the host is Office 2010 or later, regardless of “bitness”.
Instead, use #IF Win64, which is True if in 64-bit versions of Office, False if in 32-bit versions.
It’s badly named, as it indicates OFFICE bitness, not Windows bitness.
#IF Win64 Then
MsgBox “64-bit Office”
‘ And necessarily VBA7
#ELSE
MsgBox “32-bit Office”
#IF VBA7 Then
MsgBox “And it’s Office 2010 or later”
#END IF
#END IF
Catalin Bombea
Hi Steve,
Are you sure?
Declare statements that include PtrSafe work correctly in the VBA7 development environment on both 32-bit and 64-bit platforms.
Caleb
Thank you! I appreciate the help.
Mike
Subject: additional advantage of using WasteTime procedure.
Application.Wait method is not available in MS Word VBA.
Options at that point would be to add the Excel VBA library reference to the MS Word VBA project, use the API that you listed above, or use your WasteTime procedure.
Jamie
Nice article. Note that according to Microsoft’s Win32API_PtrSafe.txt file, the Sleep API declaration for VBA7 his milliseconds defined as type Long, not LongPtr.
Oliver Williams
you are wrong!
it is possible to set the wait function to less than 1 second if you simply use the following:
Application.wait (Now + (TimeValue(“0:00:01”) / 2))
As time value only accepts a set format, by dividing the delay the time is shortened to less than 1 second.
Changing the 2 for any larger figure will increase the amount the time is divided by,
Philip Treacy
Hi Oliver,
How did you confirm that this actually works?
Wait requires a parameter in Excel date format
https://docs.microsoft.com/en-us/office/vba/api/excel.application.wait
and TimeValue returns a time https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/timevalue-function
If you divide a time by 2, what is the result?
If you run these two statements
Debug.Print Now
Debug.Print (Now + (TimeValue(“0:00:01”) / 2))
You should find they give the same result.
Also, if you use the GetTickCount function and run these statements
Debug.Print GetTickCount
Application.Wait (Now + (TimeValue(“0:00:01”) / 2))
Debug.Print GetTickCount
You should find that GetTickCount returns the same value indicating that there is no delay. But if you run these statements
Debug.Print GetTickCount
Application.Wait (Now + TimeValue(“0:00:1”))
Debug.Print GetTickCount
GetTickCount will return different values indicating that there was delay.
Phil
Pete
Two problems with these examples:
1. Excel 2007 does not recognize the “PtrSafe” keyword, so #If Not VBA7, omit PtrSafe
2. The loop example will work 99% of the time, but the GetTickCount number is a long variable that will loop back to zero on overflow. If you happen to be in that wait loop when it resets to zero, you’ll be waiting for days before the loop exits.
The loop example can be simplified by omitting the NowTick variable, and changing the Loop statement to “Loop Until GetTickCount >= EndTick” but still needs to account for the GetTickCount resetting to zero problem.
The TimeValue of one second is 0.00001157, so if you want a 1/2-second delay, divide that number by 2, and use Application.Wait (Now + 0.00000579) .
Philip Treacy
Thanks for the tips Pete
Satish Shinde
I need to to make a range of cells value as 1,row by row in a certain column, then wait for 10 sec and then make the value of the cell 0 , again then move to next cell in the column and do the same thing 1 for few seconds and then 0 , before moving to next cell in a column ..
2)if I need to connect with other application in which i can use these range of values to connect with other application
Catalin Bombea
Hi Satish,
You will have to upload a sample file on our forum, where you must provide all details. What other application is involved? Another excel file, a database?
Juan Agudelo
Thank you for the tips. However, I tried the Loops version you suggested, and my Excel did not recognize GetTickCount as a valid function name. I resorted to changing the code a little:
Sub WasteTime(Seconds As Long)
Dim EndTime As Double
EndTime = Time() + Seconds / 24 / 60 / 60
Do
DoEvents
Loop Until Time() >= EndTime
End Sub
…and it works like a charm…
Philip Treacy
Hi Juan,
So the workbook I provided on the blog gives you an error saying GetTickCount is not recognised? What version of Excel and Windows are you using?
I haven’t run through your code but Seconds/24/60/60 would result in a really small number e.g. 10 seconds would give you 0.0000157
Time() returns a Date e.g. 4:52:45 PM so I’m not sure how you got this to work! 🙂
Cheers
Phil
Paul
Just a 2020 update…
Time() + Finish/24/60/60 works for me while GetTickCount throws an error
Windows 10 / Excel 365
Catalin Bombea
Hi Paul,
What error you see for GetTickCount?
Catalin
Ron Kellis
Excel 2016 64bit
Compile error
Type mismatch
The “+” is highlighted by Excel
EndTick = GetTickCount + (Finish * 1000)
Catalin Bombea
Hi Ron,
The variable types must be adjusted based on office version.
Dim NowTick As Long
Dim EndTick As Long
Should be replaced with:
#If VBA7 Then
Dim NowTick As LongPtr
Dim EndTick As LongPtr
#Else
Dim NowTick As Long
Dim EndTick As Long
#End If
Mike
THANK YOU Juan!!! You had the only code here that worked for me too. Windows 10 / Office 365… last system update 3/25/2021.
Michael
I found this explanation very helpful and delineated well. However, I’m looking for a way to cause the execution of a subroutine to take a certain amount of time. Not delayed, but slowed down.
This sort of action is always done in a PowerPoint Presentation. You simply enter the duration factor and it spends that long executing the function.
Why is this not possible in Excel? Or is it and I just haven’t heard of it yet. Thanks for your expert instruction.
Catalin Bombea
Hi Michael,
Excel is not PowerPoint. A presentation has a very clear time length.
A custom macro in excel can run for hours, in some cases, the same macro can be faster if for example the calculation is turned to manual. Depending on the amount and the complexity of the formulas you have in that workbook, inserting a row for example will take a significant amount of time to execute, it will be faster if you have less formulas, the entire calculation chain is beeing rebuild.
Therefore, it’s impossible to predict how much time is needed in your specific case. Also, the speed of code execution is out of user control, same applies to any office application.
Dan
I wish you would correct this incorrect statement: “Wait does not accept delays of less than 1 second.”
In this code example, it doesn’t, only because you used TimeValue.
To use fractions of a second just do something like:
Application.Wait (Now + ((1 / 24 / 60 / 60) * WaitSecs))
I don’t love the code examples using TimeValue because people often want to delay for less than one second, or delay for say 2.5 seconds. Using 1 second increments seems unnecessarily limiting.
Philip Treacy
Hi Dan,
I don’t follow your example. 1/24/60/60 is 1.157e-5 which is 1/86000, or 1/the number of seconds in a day? Why multiply that by the number of seconds you want to wait?
The difference between this
Debug.Print (Now)
and this
Debug.Print (Now + ((1 / 24 / 60 / 60) * 0.01))
is nothing, so I’m not seeing how
Application.Wait (Now + ((1 / 24 / 60 / 60) * 0.01))
causes a delay of less than a second.
All the information I have read says that Wait doesn’t work with periods of less than 1 second. Can you point me to a resource that does?
Regards
Phil
MD
The Loops approach worked perfectly on MacOS after I fixed a line..
From:
Loop Until NowTick >= EndTick
To:
Loop Until NowTick <= EndTick
In other words.. do something Until current time (NowTick) is lower or equal EndTick
Thank you very much for your post Philip.
Philip Treacy
Hi MD,
Not sure how that works because EndTick should be larger than NowTick
Regards
Phil
Infamous Passerby
I think Loop Until NowTick >= EndTick was correct though. it mean you will stop the loop if the NowTick was bigger or equal to the EndTick.
anon
Sub WasteTime(Sec As Long)
Dim NowTick As Date
Dim EndTick As Date
EndTick = Now + TimeSerial(0, 0, Sec)
Do
NowTick = Now
DoEvents ‘do nothing
Loop Until NowTick >= EndTick
End Sub
Jon
Tried your wonderful instruction, but could not get it to work for pausing before looping.
Please view: https://stackoverflow.com/questions/53228810/loop-following-webbrowser-document-complete
Any suggestions greatly appreciated.
Philip Treacy
Hi Jon,
As the SO reply suggest you can use
Do Until IE.readyState = 4
DoEvents
Loop
to wait for the browser.
I cover this in this post about web scraping multiple pages
Regards
Phil
James Wilk
I have this code for auto open()
Private Sub workbook_open()
Sheets(“Splash”).Select
Application.Wait (Now + TimeValue(“0:00:5”))
Sheets(“Rocket”).Select
It works fine if I manually execute the macro, but when I open the file it does not delay between the sheets :splash” and “Rocket”. and goes straight tot the end of my subroutine. What’s wrong with my quote?
Catalin Bombea
Hi James,
instead of TimeValue(“0:00:5”), Try with:
Now() + TimeSerial(0,0,5)
Artur Mario Jr
Great! In PowerPoint 2013 there isn’t anymore the wai command.
But is important to add the doComands before the sleep call if you are updating something in the window (slide in my case) or nothing will change on screen.
Many thanks!
Philip Treacy
No worries 🙂
Jon Peltier
Another very important difference between Sleep and Application.Wait on one hand and approaches that use GetTickCount or Application.OnTime on the other, is that while Sleep and Application.Wait are doing their thing, the blue spinning donut makes Excel unusable, while GetTickCount and Application.OnTime allow the user to interact with Excel the whole time they are working.
Philip Treacy
Thanks Jon 🙂
Kris
Hi Phil,
I really like your Waste Time procedure.
Is there an easy way to cancel this procedure if condition change (if i press the button for example)?
Thanks,
Kris
Catalin Bombea
Hi Kris,
You can set a dropdown with 2 values: Enabled/Disabled, and refer to this cell value in your code: If Not Thisworkbook.Worksheets.(“Sheet1”).Range(“A1″)=”Enabled” Then …
Cheers,
Catalin
Kris
Hi Catalin,
Thanks for your comment
I was thinking about cancelling the loop before EndTick value is reached if a button is pressed.
Your example will just prevent it to start if I understand it correctly.
Kris
I think I got this sorted. I had to add If statement to both CallWasteTime & WasteTime I hope this may help others.
Sub CallWasteTime()
WasteTime (10)
‘ Cancel if checkbox is True
If Worksheets(“Sheet1”).CheckBox1 = True Then
Exit Sub
End If
MsgBox “10 seconds elapsed”
End Sub
Sub WasteTime(Finish As Long)
Dim NowTick As Long
Dim EndTick As Long
EndTick = GetTickCount + (Finish * 1000)
Do
NowTick = GetTickCount
DoEvents
‘ Cancel if checkbox is True
If Worksheets(“Sheet1”).CheckBox1 = True Then
Exit Do
End If
Loop Until NowTick >= EndTick
End Sub
Catalin Bombea
Thanks for feedback Kris, glad to hear you managed to make it work!
Alan Elston
Hi,
Thanks for a very concise summarizing Blog.
Is there any significance to you using the Ticks in your Loop example?
Thanks
Alan
Philip Treacy
Hi Alan,
I you look in my example file, I’m using a Windows API function called GetTickCount which returns the number of milliseconds since the PC started.
By comparing the Ticks (millisecond count) at the start of my routine and then allowing X milliseconds to pass, I can delay the code by X milliseconds.
Regards
Phil
Alan Elston
Hi Phil,
Thanks very much for your Reply. Sorry my response is so late- I did not realize that I would get no Email response of a reply as is often the case with such things.
I realized what the Timer was from your original Blog.
I was just wondering if there was a significance to using that particular timing way, in preference to any other of the ways you described in the Loop example
Thanks
Alan
Philip Treacy
Hi Alan,
We did have a system that subscribed people comments but found that it didn’t always work well. I’m looking at a replacement.
I used the ticks just as a demonstration of another way to time the delay. You can time the delay any way you want.
Regards
Phil
Alan Elston
Thanks very much, Phil
Alan
Jomili
Philip,
I’m using 64-Bit Excel 2013. For the getTickCount declaration I had to change “LongLong” to “Long” to allow the “CallWasteTime” macro to work. Any idea why?
Philip Treacy
Hi Jomili,
I presume you mean you changed LongPtr to Long? I haven’t used LongLong in my code.
I have to ask, are you certain you are using 64 bit Excel?
Are you using 64 bit Windows? If it’s 32bit Windows then you’ll be using 32 bit Excel.
If you are using 64 bit Excel then the LongPtr type should work ok.
Regards
Phil
Dale
Why not ‘Stop’? You can use it with conditions…
Philip Treacy
Hi Dale,
Have you got an example? I thought STOP just halted execution, similar to using a breakpoint when debugging.
Regards
Phil
Me
Thanks, it was exactly what I was looking for.
Philip Treacy
You’re welcome.
Phil