• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
  • Login

Pausing or Delaying VBA Using Wait, Sleep or A Loop

You are here: Home / Excel VBA / Pausing or Delaying VBA Using Wait, Sleep or A Loop
Snail
March 30, 2015 by Philip Treacy

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.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook and follow along.

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

Snail
Philip Treacy

Microsoft Power BI Community Super User Logo

AUTHOR Philip Treacy Co-Founder / Owner at My Online Training Hub

Systems Engineer with 30+ years working for companies like Credit Suisse and E.D.S. in roles as varied as Network & Server Support, Team Leader and Consultant Project Manager.

These days Philip does a lot of programming in VBA for Excel, as well as PHP, JavaScript and HTML/CSS for web development.

He's particularly keen on Power Query where he writes a lot of M code.

When not writing blog posts or programming for My Online Training Hub, Philip can be found answering questions on the Microsoft Power BI Community forums where he is a Super User.

More Excel VBA Posts

macro to center across selection

Center Across Selection Macro

Use this Excel Center Across Selection shortcut macro generated by ChatGPT instead of merge and center.
Display All Matches from Search in Userform ListBox

Display All Matches from Search in Userform ListBox

Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
animating excel charts

Animating Excel Charts

Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
dynamic data validation lists in userforms

Dynamic Data Validation Lists in Userforms

Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
show report filter pages for power pivot pivottables

Show Report Filter Pages for Power Pivot PivotTables

PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
charting real time data in excel

Charting Real Time Data in Excel

Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
select multiple items from drop down data validation list

Select Multiple Items from Drop Down (Data Validation) List

Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms

Multi-Language Excel Calendar (Date Picker) for Worksheets and Userforms

Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
automating and emailing pivot table reports

Automating and Emailing Pivot Table Reports

Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
search for data with userform

Searching for Data With a User Form

Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.


Category: Excel VBA
Previous Post:Excel Quick AnalysisExcel Quick Analysis
Next Post:Charting Variances in ExcelExcel variance charts

Reader Interactions

Comments

  1. Jim Frangos

    December 11, 2022 at 12:09 pm

    I am interested in .NET code to open a local .XLSX file which is 32-bit.
    JIM

    Reply
    • Philip Treacy

      December 12, 2022 at 2:53 pm

      Hi Jim,

      Sorry we don’t do any .NET programming.

      Regards

      Phil

      Reply
  2. Deepak

    November 5, 2022 at 11:27 am

    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

    Reply
    • Philip Treacy

      November 8, 2022 at 4:23 pm

      Hi Deepak,

      This line selects a cell on the last row

      Range("Z1").End(xlDown).Select

      then 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:=False

      Regards

      Phil

      Reply
  3. sunil

    June 13, 2022 at 4:16 pm

    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

    Reply
    • Philip Treacy

      June 15, 2022 at 8:23 pm

      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

      Reply
      • Sunil

        June 17, 2022 at 4:29 pm

        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.

        Reply
        • Mynda Treacy

          June 17, 2022 at 7:32 pm

          Hi Sunil, please post your question on our Excel forum where you can also upload a sample file and we can help you further.

          Reply
  4. Yuvraj

    June 1, 2022 at 1:16 pm

    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.

    Reply
    • Catalin Bombea

      June 1, 2022 at 2:11 pm

      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

      Reply
  5. Max

    April 13, 2022 at 6:52 pm

    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

    Reply
    • Philip Treacy

      April 18, 2022 at 2:39 pm

      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

      Reply
  6. Boris Pretzel

    March 14, 2022 at 10:22 pm

    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?

    Reply
    • Catalin Bombea

      March 15, 2022 at 2:17 am

      Yes, you can just ignore the error, the compiler will still raise that error.

      Reply
  7. Rik van Zomeren

    March 3, 2022 at 9:20 pm

    Code not working anymore excel 2016 windows 10;

    WasteTime(Finish As Long):

    I’m getting compile error:
    Type mismatch

    Reply
    • Catalin Bombea

      March 9, 2022 at 2:00 am

      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

      Reply
  8. Geoff Strickler

    October 17, 2021 at 2:44 pm

    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.

    Reply
    • Catalin Bombea

      October 18, 2021 at 1:05 pm

      Unfortunately, that function is a windows function only, there is no similar library in Mac.

      Reply
  9. Frederic Biarnes

    July 3, 2021 at 7:53 pm

    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

    Reply
    • Philip Treacy

      July 5, 2021 at 11:54 am

      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

      Reply
  10. Chip

    May 28, 2021 at 3:54 pm

    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. 😉

    Reply
    • Philip Treacy

      May 31, 2021 at 11:22 am

      Chip,

      Did you download and open the example workbook I provided in this blog post? It includes the declaration for GetTickCount.

      Regards

      Phil

      Reply
  11. Khan

    December 29, 2020 at 10:28 pm

    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.

    Reply
    • Philip Treacy

      December 30, 2020 at 9:02 am

      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

      Reply
  12. AP

    December 28, 2020 at 11:05 am

    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

    Reply
  13. Mike

    October 13, 2020 at 5:20 am

    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.

    Reply
    • Philip Treacy

      October 13, 2020 at 10:34 am

      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

      Reply
  14. Sophie Kernahan

    September 29, 2020 at 7:35 pm

    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.

    Reply
    • Philip Treacy

      September 30, 2020 at 10:41 am

      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 Sub
      
      

      If you are still having issues please start a topic on the forum and attach your workbook.

      Regards

      Phil

      Reply
  15. Dan Pack

    July 6, 2020 at 10:51 am

    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

    Reply
    • Philip Treacy

      July 6, 2020 at 3:11 pm

      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

      Reply
  16. José Andrés

    June 18, 2020 at 9:26 am

    Thank you very much!

    Reply
    • Philip Treacy

      June 18, 2020 at 1:47 pm

      You’re welcome

      Reply
  17. vknowles

    February 18, 2020 at 6:27 am

    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.

    Reply
    • Philip Treacy

      February 18, 2020 at 1:47 pm

      Thanks 🙂

      Reply
  18. Steve Rindsberg

    September 18, 2019 at 1:51 am

    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

    Reply
    • Catalin Bombea

      September 18, 2019 at 4:00 am

      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.

      Reply
  19. Caleb

    July 13, 2019 at 4:20 am

    Thank you! I appreciate the help.

    Reply
  20. Mike

    June 14, 2019 at 12:45 am

    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.

    Reply
  21. Jamie

    June 11, 2019 at 12:21 am

    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.

    Reply
  22. Oliver Williams

    May 26, 2019 at 3:28 am

    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,

    Reply
    • Philip Treacy

      May 26, 2019 at 1:27 pm

      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

      Reply
      • Pete

        March 4, 2021 at 2:45 am

        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) .

        Reply
        • Philip Treacy

          April 20, 2021 at 2:25 pm

          Thanks for the tips Pete

          Reply
  23. Satish Shinde

    May 22, 2019 at 1:18 am

    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

    Reply
    • Catalin Bombea

      May 23, 2019 at 1:20 pm

      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?

      Reply
  24. Juan Agudelo

    May 15, 2019 at 11:00 pm

    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…

    Reply
    • Philip Treacy

      May 16, 2019 at 5:10 pm

      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

      Reply
      • Paul

        June 11, 2020 at 3:45 am

        Just a 2020 update…

        Time() + Finish/24/60/60 works for me while GetTickCount throws an error

        Windows 10 / Excel 365

        Reply
        • Catalin Bombea

          July 4, 2020 at 6:23 pm

          Hi Paul,
          What error you see for GetTickCount?
          Catalin

          Reply
          • Ron Kellis

            September 30, 2021 at 1:06 am

            Excel 2016 64bit
            Compile error
            Type mismatch

            The “+” is highlighted by Excel

            EndTick = GetTickCount + (Finish * 1000)

          • Catalin Bombea

            October 5, 2021 at 7:31 pm

            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

      March 26, 2021 at 5:59 am

      THANK YOU Juan!!! You had the only code here that worked for me too. Windows 10 / Office 365… last system update 3/25/2021.

      Reply
  25. Michael

    May 3, 2019 at 3:16 pm

    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.

    Reply
    • Catalin Bombea

      May 3, 2019 at 3:52 pm

      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.

      Reply
  26. Dan

    January 30, 2019 at 4:41 am

    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.

    Reply
    • Philip Treacy

      January 30, 2019 at 1:57 pm

      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

      Reply
  27. MD

    December 16, 2018 at 4:34 am

    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.

    Reply
    • Philip Treacy

      December 16, 2018 at 9:42 am

      Hi MD,

      Not sure how that works because EndTick should be larger than NowTick

      Regards

      Phil

      Reply
    • Infamous Passerby

      December 26, 2018 at 5:57 pm

      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.

      Reply
  28. anon

    November 10, 2018 at 5:10 am

    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

    Reply
  29. Jon

    November 10, 2018 at 1:40 am

    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.

    Reply
    • Philip Treacy

      November 10, 2018 at 9:07 am

      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

      Reply
  30. James Wilk

    August 4, 2018 at 12:13 am

    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?

    Reply
    • Catalin Bombea

      August 4, 2018 at 2:07 pm

      Hi James,
      instead of TimeValue(“0:00:5”), Try with:
      Now() + TimeSerial(0,0,5)

      Reply
  31. Artur Mario Jr

    July 28, 2018 at 3:11 am

    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!

    Reply
    • Philip Treacy

      July 29, 2018 at 9:32 am

      No worries 🙂

      Reply
  32. Jon Peltier

    May 2, 2018 at 12:28 am

    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.

    Reply
    • Philip Treacy

      May 2, 2018 at 9:42 am

      Thanks Jon 🙂

      Reply
  33. Kris

    March 15, 2018 at 6:45 pm

    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

    Reply
    • Catalin Bombea

      March 17, 2018 at 3:31 pm

      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

      Reply
      • Kris

        March 20, 2018 at 2:09 am

        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.

        Reply
        • Kris

          March 20, 2018 at 3:04 am

          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

          Reply
          • Catalin Bombea

            March 20, 2018 at 3:27 pm

            Thanks for feedback Kris, glad to hear you managed to make it work!

  34. Alan Elston

    November 12, 2016 at 6:17 pm

    Hi,
    Thanks for a very concise summarizing Blog.
    Is there any significance to you using the Ticks in your Loop example?
    Thanks
    Alan

    Reply
    • Philip Treacy

      November 13, 2016 at 8:20 pm

      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

      Reply
      • Alan Elston

        December 4, 2016 at 9:56 pm

        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

        Reply
        • Philip Treacy

          December 5, 2016 at 4:26 pm

          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

          Reply
          • Alan Elston

            December 5, 2016 at 6:47 pm

            Thanks very much, Phil
            Alan

  35. Jomili

    October 20, 2016 at 12:00 am

    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?

    Reply
    • Philip Treacy

      October 20, 2016 at 10:20 am

      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

      Reply
  36. Dale

    September 21, 2016 at 12:06 am

    Why not ‘Stop’? You can use it with conditions…

    Reply
    • Philip Treacy

      October 5, 2016 at 2:56 pm

      Hi Dale,

      Have you got an example? I thought STOP just halted execution, similar to using a breakpoint when debugging.

      Regards

      Phil

      Reply
  37. Me

    December 15, 2015 at 5:19 am

    Thanks, it was exactly what I was looking for.

    Reply
    • Philip Treacy

      December 15, 2015 at 10:18 am

      You’re welcome.

      Phil

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Popular Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel Office Scripts
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook

Sign up to our newsletter and join over 400,000
others who learn Excel and Power BI with us.

 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate
  • Sponsor Our Newsletter

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x