Excel VBA Wait Milliseconds

Get FREE Advanced Excel Exercises with Solutions!

In our day-to-day Excel VBA execution process, we sometimes need to reevaluate the code while running to see whether the value output is in the order. And at the same time control the code running process, to determine whether the running code does not have any kind of error or faulty output. Inserting a time delay will ease the process for the user. In this article, we are going to show various VBA methods in Excel to wait milliseconds in between code execution.

Here we show a code using which we can determine the output random values in the worksheet. The process slowed by the time set by us in the first place.


How to Launch VBA Editor in Excel

You can easily launch the VBA editor by pressing Alt+F11 or using the Visual Basic command from the Developer tab. As shown in the video below,

VBA editor launch

  • After entering the Developer tab, then go to Insert > Module, and you will see an editor where you can put the necessary code.

Module Initiation

Note:

In any case, if you can’t find the Developer tab in the ribbon, you need to enable it from the options. You can easily enable the Developer tab by following this helper article.


Excel VBA Wait Milliseconds: 3 Examples

We can opt for various types of codes to insert a delay section inside the code that will make the code execution delay by milliseconds. To avoid any kind of compatibility issue, try to opt for the Excel 365 edition.


1. Using Sleep Property to Review Process While Running Code

We can use the Sleep property Excel function to review a certain process by periodically placing time delay. That small window of delay will allow users to review whether their data is correctly entered or not. Below we presented an example where we will place a random number in the worksheet and then ask the user whether the number is even or not. Right before the question, there will be a small delay to give the user a chance to evaluate whether the number is Even or not. After deciding for the user, the code will return meaning message according to the user’s decision.

Steps:

  • First, you need to enter the following code in the code editor:
Declare PtrSafe Sub Sleep Lib "kernelbase" (ByVal milliseconds As LongPtr)
Sub wait_milisecond()
Dim MinValue As Integer
Dim MaxValue As Integer
Dim RandomNumber As Integer
MinValue = 1
MaxValue = 10
ActiveSheet.Range("C3").Value = Int((MaxValue - MinValue + 1) * Rnd + MinValue)
Sleep 2000
UserResponse = MsgBox("Do you think this value is integer?, If it is Integer then press Yes", vbYesNo)
If UserResponse = vbYes Then
GoTo Line1
Else
MsgBox "The Value is Odd"
MsgBox "Quitting the program..."
End
End If
Line1:
MsgBox "The value is Even"
End Sub

 

VBA Code Breakdown

Declare PtrSafe Sub Sleep Lib “kernelbase” (ByVal milliseconds As LongPtr)

  • This line declares a sleep function from the “kernelbase” library that is used to pause the execution of the code for a given number of milliseconds.

Sub wait_milisecond()

  • This line starts the definition of a new Sub procedure named “wait_milisecond”.

    Dim MinValue As Integer

  • This line declares an integer variable named “MinValue”.

    Dim MaxValue As Integer

  • This line declares an integer variable named “MaxValue”.

 Dim RandomNumber As Integer

  • This line declares an integer variable named “RandomNumber”.

    MinValue = 1

  • This line assigns the value 1 to the “MinValue” variable.

    MaxValue = 10

  • This line assigns the value 10 to the “MaxValue” variable.

ActiveSheet.Range(“C3”).Value = Int((MaxValue – MinValue + 1) * Rnd + MinValue)

  • This line generates a random integer between 1 and 10 and stores it in cell C3 of the active sheet.

Sleep 2000

  • This line pauses the execution of the code for 2000 milliseconds (2 seconds).

   UserResponse = MsgBox(“Do you think this value is integer?, If it is Integer then press Yes”, vbYesNo)

  • This line displays a message box asking the user whether they think the value in cell C3 is an integer or not and stores the user’s response in the “UserResponse” variable.

        If UserResponse = vbYes Then

  • This line starts an If statement that checks whether the user responded “Yes” to the message box.

GoTo Line1

  • This line jumps to the “Line1” label if the user responded “Yes” to the message box.

    Else

  • This line starts an Else clause that executes if the user did not respond “Yes” to the message box.

        MsgBox “The Value is Odd”

  • This line displays a message box informing the user that the value in cell C3 is odd.

        MsgBox “Quitting the program…”

  • This line displays a message box informing the user that the program is quitting.

        End

  • This line terminates the execution of the program.

    End If

  • This line ends the If statement.

Line1:

  • This line defines a label named “Line1”.

MsgBox “The value is Even”

  • This line displays a message box informing the user that the value in cell C3 is even.

End Sub

  • Right after entering the code, press the Run command from the code editor.
  • There will be a random number generated in cell C3.
  • And then there will be a brief period of delay, in that period user need to determine whether the number is even or odd.
  • After that brief period of delay, there will be a message box asking the user whether the number is Even or Odd.
  • The user here needs to choose the option. In our case, we found that number to be even and so we entered Even.
  • After pressing Even, we found that there is a message box showing that the number is Even.
  • So this way we can create a time delay between the code execution phase and take important decisions like this.

2. Using the Application.Wait Property to Allow the User to Cancel the Process

Now we can use the Application.Wait property to allow the user to check out a procedure while running and the ability to cancel the procedure as a whole if needed.

Steps

  • For this, first, open the code editor as shown in the first part of the article and then enter the following code:
Sub review_process()
For i = 1 To 10
ActiveSheet.Cells(i, 2).Value = Int((100 * Rnd) + 1)
Application.Wait (Now + (1000 * 0.00000001))
Next i
End Sub

VBA Code Breakdown

Sub review_process()

  • This line defines a new subroutine called review_process.

For i = 1 To 10

This line starts a loop that will repeat 10 times, with the variable i is set to each integer value from 1 to 10.

ActiveSheet.Cells(i, 2).Value = Int((100 * Rnd) + 1)

  • This line sets the value of the cell in column 2 and row i on the active worksheet to a random integer between 1 and 100.

 Application.Wait (Now + (1000 * 0.00000001))

  • This line causes the code to pause execution for a brief period of time, specifically for 1/100,000,000th of a second (1 nanosecond). This is accomplished by adding a small fraction of a second to the current time using the Now function and then waiting until that time is reached.

Next i

  • This line signals the end of the loop and increments i to the next integer value.

End Sub

  • This line signals the end of the subroutine.
  • After entering the code you will see that random number start filling up the range of cell C3:C12.
  • And if you notice closely then you see that the number filling up has some delay within it. As we set it in the 4th line in the code editor. A second here is 1000 milliseconds. So every number filling up has a 1-sec gap between them. So the number being poured into.

And this is how users can use the Application.Wait property to induce a milliseconds delay or wait in Excel VBA macro.


3. Use of DoEvents to Loop Through Update Database

We also can use DoEvents property to loop through the system to update the database by sourcing the data from one place to another place. Below we will do the same thing of updating the database from a given source.

Steps

  • For this, we presented a sample dataset under the source column header. And we want to update the data in the destination column header.
  • For this, we can open the code editor from the developer tab as shown above.
  • And then paste the below code and then click on the Run command shown in the above of code editor window.
Sub Update_Database()
Dim sourceColumn As Range
Dim targetColumn As Range
Dim sourceCell As Range
Dim targetCell As Range
Dim lastRow As Long
Dim i As Long
Set sourceColumn = Range("B3:B12")
Set targetColumn = Range("C3:C12")
lastRow = sourceColumn.Cells(sourceColumn.Cells.Count).Row
For i = 1 To lastRow
Set sourceCell = sourceColumn.Cells(i)
Set targetCell = targetColumn.Cells(i)
targetCell.Value = sourceCell.Value
Call WasteTime
Next i
End Sub
Sub WasteTime()
Dim EndTime As Date
EndTime = Now + TimeValue("00:00:2")
Do While Now < EndTime
DoEvents
Loop
End Sub
  • After clicking the Run command, we can see that the data are being copied into the destination column.
  • And at the same time we can see that the data are being updated to the destination column header are updating with a time gap of like 2 seconds.
  • In between updating the values, the time gap allows the user to review the values that are being updated. Users can stop the process anytime while reviewing the process.

VBA Code Breakdown

Sub Update_Database()

Defines a Sub procedure named “Update_Database”, which is a block of code that performs a specific task.

    Dim sourceColumn As Range

    Dim targetColumn As Range

    Dim sourceCell As Range

    Dim targetCell As Range

    Dim lastRow As Long

    Dim i As Long

Declares several variables that will be used in the procedure, all of which are of the Range or Long data types.

    Set sourceColumn = Range(“B3:B12”)

    Set targetColumn = Range(“C3:C12”)

Assigns the “sourceColumn” and “targetColumn” variables to the ranges B3:B12 and C3:C12, respectively.

    lastRow = sourceColumn.Cells(sourceColumn.Cells.Count).Row

Assigns the “lastRow” variable to the row number of the last cell in the “sourceColumn” range.

    For i = 1 To lastRow

        Set sourceCell = sourceColumn.Cells(i)

        Set targetCell = targetColumn.Cells(i)

        targetCell.Value = sourceCell.Value

        Call WasteTime

    Next i

Loops through each cell in the “sourceColumn” range, assigns the corresponding cell in the “targetColumn” range to have the same value, and calls the “WasteTime” subroutine before moving to the next cell.

End Sub

Sub WasteTime()

Defines a Sub procedure named “WasteTime”.

    Dim EndTime As Date

    EndTime = Now + TimeValue(“00:00:2”)

Declares the “EndTime” variable as a Date data type and assigns it to the current time plus two seconds.

    Do While Now < EndTime

        DoEvents

    Loop

End Sub

  • Pauses the execution of the code until the current time is greater than or equal to the “EndTime” value, using the DoEvents statement to allow the system to process other events during the wait.

And in this way, we can induce a milliseconds delay or wait in Excel VBA macro.

Read More: Excel VBA DoEvents and Wait Properties


Excel VBA Wait for MiliSecond Code for Windows 32 and 64 Separately

When using the various method mentioned above, we may face problems where we need to face errors where need to resolve issues related to the Windows version. Meaning some code will not run in the Windows 32 edition. So we need to modify the code for Windows 64. On the other hand, we may need to modify the 64 edition code for the 32 edition code as some people may still use Windows 32 or prefer the Windows 32 version. Both of the problems will be addressed and resolved by the below code.

Steps

  • First, open the code editor and enter the below code shown below,
#If VBA7 Then
Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal milliseconds As LongPtr)
#Else
Declare Sub Sleep Lib "kernel32" (ByVal milliseconds As Long)
#End If
Sub Delay_milisecond_for_win32_win64()
Dim MinValue As Integer
Dim MaxValue As Integer
Dim RandomNumber As Integer
MinValue = 1
MaxValue = 10
ActiveSheet.Range("C3").Value = Int((MaxValue - MinValue + 1) * Rnd + MinValue)
Sleep 2000
UserResponse = MsgBox("Do you think this value is integer?, If it is Integer then press Yes", vbYesNo)
If UserResponse = vbYes Then
GoTo Line1
Else
MsgBox "The Value is Odd"
MsgBox "Quitting the program..."
End
End If
Line1:
MsgBox "The value is Even"
End Sub

VBA Code Breakdown+

#If VBA7 Then

    Declare PtrSafe Sub Sleep Lib “kernel32” (ByVal milliseconds As LongPtr)

#Else

    Declare Sub Sleep Lib “kernel32” (ByVal milliseconds As Long)

#End If

  • This is a conditional compilation directive that checks if the version of VBA being used is VBA7 or higher. If it is, it declares a pointer-safe version of the Sleep function that takes a 64-bit LongPtr argument. If not, it declares a non-pointer-safe version that takes a 32-bit long argument. The Sleep function is used later in the code to introduce a delay.

Sub Delay_milisecond_for_win32_win64()

  • This line declares a new subroutine called Delay_milisecond_for_win32_win64.

Dim MinValue As Integer

Dim MaxValue As Integer

Dim RandomNumber As Integer

  • These lines declare three integer variables to store the minimum value, maximum value, and the generated random number.

MinValue = 1

MaxValue = 10

  • These lines assign the values of 1 and 10 to the MinValue and MaxValue variables, respectively.

ActiveSheet.Range(“C3”).Value = Int((MaxValue – MinValue + 1) * Rnd + MinValue)

  • This line generates a random integer between the values of MinValue and MaxValue, inclusive, and assigns it to cell C3 of the active worksheet.

Sleep 2000

  • This line uses the Sleep function to pause the execution of the program for 2000 milliseconds (or 2 seconds).

UserResponse = MsgBox(“Do you think this value is integer?, If it is Integer then press Yes”, vbYesNo)

  • This line displays a message box with a prompt asking the user if they believe the value in cell C3 is an integer. The message box contains two buttons: “Yes” and “No”. The user’s response is stored in the UserResponse variable.

If UserResponse = vbYes Then

    GoTo Line1

Else

    MsgBox “The Value is Odd”

    MsgBox “Quitting the program…”

    End

End If

This If statement checks if the user clicked the “Yes” button. If so, the program jumps to the Line1 label. If not, the program displays a message box saying that the value is odd, displays another message box saying that the program is quitting, and then exits. Set the Delay Manually Using an Inputbox

Line1:

MsgBox “The value is Even”

This line declares a label called Line1 and then displays a message box saying that the value in cell C3 is even.

  • After entering the code, press the Run command from the code editor.
  • There will be a random number generated in cell C3.
  • And then there will be a brief period of delay, in that period user need to determine whether the number is even or odd.
  • After that brief period of delay, there will be a message box asking the user whether the number is Even or Odd.
  • The user here needs to choose the option. In our case, we found that number to be even and so we entered Even.
  • After pressing Even, we found that there is a message box showing that the number is Even.
  • So this way we can create a time delay between the code execution phase and take important decisions like this.

We can induce a milliseconds delay or wait in Excel VBA macro for the Windows 32 and 64 editions at the same time.


Set the Delay Manually Using an Inputbox

All the above methods and codes mentioned here are about setting the delay inside the code. But we also can set the delay manually using an Input Box. The code will show a delay according to this delay mentioned in the InputBox.

Steps

  • We need to first open the code editor window and then paste the code shown below, then press Run.
Declare PtrSafe Sub Sleep Lib "kernelbase" (ByVal milliseconds As LongPtr)
Sub wait_milisecond()
    Dim MinValue As Integer
    Dim MaxValue As Integer
    Dim RandomNumber As Integer
    MinValue = 1
    MaxValue = 10
    Delay = InputBox("Enter the Delay Amount in Milisecond", "Wait Milisecond", "")
    ActiveSheet.Range("C3").Value = Int((MaxValue - MinValue + 1) * Rnd + MinValue)
    Sleep Delay
    UserResponse = MsgBox("Do you think this value is integer?, If it is Integer then press Yes", vbYesNo)
    If UserResponse = vbYes Then
        GoTo Line1
    Else
        MsgBox "The Value is Odd"
        MsgBox "Quitting the program..."
        End
    End If
Line1:
    MsgBox "The value is Even"
End Sub
  • We will see an input box asking for the Delay We enter for 2 seconds.
  • There will be a random number generated in cell C3.
  • And then there will be a brief period of delay, in that period user need to determine whether the number is even or odd.
  • After that brief period of delay, there will be a message box asking the user whether the number is Even or Odd.
  • The user here needs to choose the option. In our case, we found that number to be even and so we entered Even.
  • After pressing Even, we found that there is a message box showing that the number is Even.
  • So this way we can create a time delay between the code execution phase and take important decisions like this.

VBA Code Breakdown

Declare PtrSafe Sub Sleep Lib “kernelbase” (ByVal milliseconds As LongPtr)

  • This line declares a pointer-safe version of the Sleep function that takes a 64-bit LongPtr argument. The Sleep function is used later in the code to introduce a delay. Note that this version of the function is located in the “kernelbase” library, which is a part of the Windows API.

Sub wait_milisecond()

This line declares a new subroutine called wait_milisecond.

    Dim MinValue As Integer

    Dim MaxValue As Integer

    Dim RandomNumber As Integer

These lines declare three integer variables to store the minimum value, maximum value, and the generated random number.

    MinValue = 1

    MaxValue = 10

These lines assign the values of 1 and 10 to the MinValue and MaxValue variables, respectively.

    Delay = InputBox(“Enter the Delay Amount in Milisecond”, “Wait Milisecond”, “”)

This line displays an input box asking the user to enter the amount of delay they want, in milliseconds. The value entered by the user is stored in the Delay variable.

    ActiveSheet.Range(“C3”).Value = Int((MaxValue – MinValue + 1) * Rnd + MinValue)

This line generates a random integer between the values of MinValue and MaxValue, inclusive, and assigns it to cell C3 of the active worksheet.

    Sleep Delay

This line uses the Sleep function to pause the execution of the program for the number of milliseconds specified by the Delay variable.

    UserResponse = MsgBox(“Do you think this value is integer?, If it is Integer then press Yes”, vbYesNo)

This line displays a message box with a prompt asking the user if they believe the value in cell C3 is an integer. The message box contains two buttons: “Yes” and “No”. The user’s response is stored in the UserResponse variable.

    If UserResponse = vbYes Then

        GoTo Line1

    Else

        MsgBox “The Value is Odd”

        MsgBox “Quitting the program…”

        End

    End If

This If statement checks if the user clicked the “Yes” button. If so, the program jumps to the Line1 label. If not, the program displays a message box saying that the value is odd, displays another message box saying that the program is quitting, and then exits.

Line1:

    MsgBox “The value is Even”

End Sub

This line declares a label called Line1 and then displays a message box saying that the value in cell C3 is even. And this is how we can introduce a milliseconds delay or wait in Excel VBA.


Frequently Asked Questions

Does clock () return milliseconds?

The CLOCK function in Excel does not return milliseconds. The CLOCK() function returns the current time as a serial number that represents the number of seconds since midnight. The function updates once per minute, so it does not provide millisecond precision.

If you need to capture the current time with millisecond precision, you can use a combination of the NOW() function and a custom number format. The NOW() function returns the current date and time as a serial number, with millisecond precision. To display the time with millisecond precision, you can apply a custom number format to the cell that uses the “hh:mm:ss.000” format code.

For example, you can use the following formula to capture the current time with millisecond precision:

=NOW()

Then, you can format the cell with the customer number format “hh:mm:ss.000” to display the time with millisecond precision as shown below,

show milisecond in Excel

How do I ignore milliseconds in Excel?

To ignore milliseconds in Excel, you can follow the below process.

  • Select the cells that contain the time values you want to format.
  • Then go to Home > Number. Then from the drop-down menu, select the Time option.

Avoid Milisecond from ribbon menu

This will eventually change the time format from millisecond to normal second-minute format.


Things to Remember

  1. Consider the purpose of the delay: Why do you need to introduce a milliseconds delay or wait in Excel VBA? Is it to provide time for a process to complete or to synchronize with another process? Understanding the purpose of the delay will help you determine the appropriate length of time for the delay and the best method to use.
  2. Use the appropriate function or method: Many programming languages have built-in functions or methods that allow you to introduce a delay or wait in your code. These functions are often designed to provide an accurate delay time and to minimize processor usage while waiting.
  3. Be mindful of the impact on system performance: Introducing a delay in your code can cause your program to become unresponsive or use excessive system resources. When using a delay, it’s important to consider the impact on system performance and to ensure that the delay is not causing other processes to be delayed or blocked.
  4. Consider using asynchronous programming techniques: If you need to wait for a long-running process to complete, consider using asynchronous programming techniques. This allows your program to continue running while the long-running process executes in the background, improving overall performance and responsiveness.
  5. Test and optimize the delay time: The length of the delay will depend on a variety of factors, including system performance and the specific requirements of your program. It’s important to test and optimize the delay time to ensure it provides the necessary delay without causing unnecessary delays or slowing down your program.

Download Practice Workbook

You can download the Excel workbook from here.


Conclusion

In this article, you can see that there are multiple ways to execute a function with a milliseconds delay or wait in Excel VBA. Those delays allow the user to review the code input and assert control over the execution process. Users need to understand the delay processes and use them according to their needs.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Rubayed Razib Suprov
Rubayed Razib Suprov

Rubayed Razib, holding a BSC degree in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a devoted member of the ExcelDemy project. He has contributed significantly by authoring numerous articles and showcasing proficiency in VBA. Razib efficiently automates Excel challenges using VBA macros and actively participates in the ExcelDemy forum, providing valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo