Excel VBA Wait Milliseconds

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,

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

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

• 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

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

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

• 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:

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,

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.

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

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