Delay macro to allow events to finish

by

Instead I implemented the delay using the functions Now and DateAdd:

Function Delay(Seconds As Long)
    Dim StopTime As Date: StopTime = DateAdd("s", Seconds, Now)
    Do While Now < StopTime
        DoEvents
    Loop
End Function

Updated below for millisecond precision

Function Delay(Seconds As Double)
    Dim StopTime As Double: StopTime = Timer + Seconds
    Do While Timer < StopTime
        DoEvents
    Loop
End Function

For asynchronous events, the following may work but is currently untested

Function DelayForAsync()
    Dim CalculationState As Long
    With Application
        CalculationState = .Calculation
        .Calculation = xlCalculationAutomatic
        .CalculateUntilAsyncQueriesDone
        Do Until .CalculationState = xlDone
            DoEvents
        Loop
        .Calculation = CalculationState
    End With
End Function

More Related Contents:

  1. How do you change the value of a cell in excel by clicking on it using VBA? [closed]
  2. Excel Number Separation [closed]
  3. excel vba compare & copy worksheets from 2 excel files
  4. VBA Stuck after certain loop
  5. How do I use FileSystemObject in VBA?
  6. Is the . in .Range necessary when defined by .Cells?
  7. How to return a result from a VBA function
  8. Cycle through sub-folders and files in a user-specified root directory [duplicate]
  9. Convert Text to Date?
  10. Loop through each cell in a range of cells when given a Range object
  11. Properly Handling Errors in VBA (Excel)
  12. Password hash function for Excel VBA
  13. Is there a way to slow down a Web Scraper so it will pick up the code?
  14. Create a folder and sub folder in Excel VBA
  15. Aggregate, Collate and Transpose rows into columns
  16. VBA Excel sort range by specific column
  17. Select method of Range class failed via VBA
  18. Renaming Files with Excel VBA
  19. Delete cells in an Excel column when rows = 0
  20. Is there a way to import data from .csv to active excel sheet?
  21. Comparison of Dictionary, Collections and Arrays
  22. Break out of a While…Wend loop
  23. How to add events to dynamically created controls (buttons, listboxes) in Excel VBA userform
  24. How to make safe API Timers in VBA?
  25. Error 1004 when setting Range.Locked
  26. How to extract groups of numbers from a string in vba
  27. Create folder path if does not exist (saving from VBA)
  28. Access files with long paths (over 260)
  29. Concatenate multiple ranges using vba
  30. How to fix a VBA “type mismatch” error after switching to 64-bit Excel

Leave a Comment