How to Use VBA Methods in Excel to Wait Milliseconds between Code Execution (3 Ways)

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:

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

Method 1 – Using Sleep Property to Review Process While Running Code

Steps:

• 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

• After entering the code, press the Run command from the code editor.
• A random number will be generated in cell C3.
• There will be a brief delay. During that period, the user must determine whether the number is even or odd.
• After that brief delay, a message box will ask the user whether the number is Even or Odd.
• The user here needs to choose the option. We found that number to be even in our case, so we entered Even.
• After pressing Even, we found a message box showing that the number was Even.
• This way, we can create a time delay between the code execution phase and make important decisions like this.

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

Steps

• 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

• This line signals the end of the subroutine.
• After entering the code, you will see that random number filling up the range of cell C3:C12.
• Looking closely, you see that the number filling up has some delay. 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.

Method 3 – Using DoEvents to Loop Through Update Database

Steps:

• We have a sample dataset under the source column header. We want to update the data in the destination column header.
• As shown above, we can open the code editor from the developer tab.
• Paste the below code and then click on the Run command shown in the above 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 see that the data are copied into the destination column.
• At the same time, we can see that the data are being updated to the destination column header with a time gap of about 2 seconds.
• The time gap allows the user to review the values being updated between updates.

Read More: Excel VBA DoEvents and Wait Properties

How to Apply an Excel VBA Wait for MiliSecond Code for Windows 32 and 64

Steps

• Open the code editor and enter the 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

• After entering the code, press the Run command from the code editor.
• A random number will be 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, a message box will ask 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, so we entered Even.
• After pressing Even, we found a message box showing that the number is Even.
• We can create a time delay between the code execution phase and take important decisions like this.

Set the Delay Manually Using an Inputbox

Steps

• Open the code editor window and then paste the code shown below:
• 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 about the delay. Press enter for 2 seconds.
• A random number will be 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, a message box will ask 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, so we entered Even.
• After pressing Even, we found a message box showing that the number is Even.
• 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, maximum, and generated random numbers.

    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 program’s execution 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 the value is odd, 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. We can introduce a millisecond delay or wait in Excel VBA.

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 ensure that the delay does not cause 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 various factors, including system performance and your program’s specific requirements. Testing and optimizing the delay time is important to ensure it provides the necessary delay without causing unnecessary delays or slowing down your program.

Download the Practice Workbook

You can download the Excel workbook from here.

Related Articles

• Excel VBA DoEvents Alternative
• How to Use Excel VBA to Wait Until Refresh Complete
• Excel VBA: Wait Until a Process Complete