Microsoft Excel is a powerful software. We can perform numerous operations on our datasets using excel tools and features. There are many default Excel Functions that we can use to create formulas. Many educational institutions and business companies use excel files to store valuable data. Sometimes, we may have to refresh certain values for generating updated output after a fixed interval. There are a few methods available to auto-update excel worksheets. But you’ve to apply VBA if the interval period is in seconds. This article will show you 4 ideal examples of Auto Update with Interval 5 Seconds in Excel.
Download Practice Workbook
Download the following workbook to practice by yourself.
4 Ideal Examples of Auto Update with Interval 5 Seconds in Excel
To auto-refresh certain data values after every specified second, we’ve to take the help of Excel VBA. No other way is available when the interval period is in seconds. A couple of other ways are available for the minutes. In this article, we’ll only consider the interval in second cases. To illustrate, we’ll use the following dataset as an example. For instance, we have Value 1 & Value 2 which we generate using the RAND function.
In cell C6, we apply the NOW function to get the Time.
Lastly, in cell C7, we input the TODAY function for the Date.
1. Auto Update Cell Every 5 Seconds with Excel VBA
In our first example, you’ll see how to auto-update a single cell every 5 seconds. Therefore, follow the steps below to perform the task.
STEPS:
- First, go to the Development tab.
- Then, select Visual Basic.
- As a result, the VBA window will pop out.
- There, select the Insert tab.
- After that, click Module from the drop-down.
- Consequently, the Module window will appear.
- Copy the following code and paste it into the box.
Sub UpdateCell()
Worksheets(1).Calculate
Range("C4").Calculate
Application.OnTime DateAdd("s", 5, Now), "UpdateCell"
End Sub
- Now, save the file.
- Next, press the F5 key to run the code.
- Thus, it’ll refresh cell C4 every 5 seconds.
Read More: How to Refresh Excel Sheet Automatically Using VBA (4 Methods)
2. Apply VBA Code to Refresh Cell Range with Interval 5 Seconds
Moreover, you may want to refresh a cell range instead of just a cell. Here, you’ll learn the VBA code to carry out that operation. So, learn the following process.
STEPS:
- Firstly, go to Development ➤ Visual Basic.
- Afterward, click Insert ➤ Module.
- The Module window will emerge.
- Copy the below code and paste it there.
Sub UpdateCellRange()
Worksheets(2).Range("C4:C7").Calculate
Application.OnTime DateAdd("s", 5, Now), "UpdateCellRange"
End Sub
- Next, save the code and press F5.
- As a result, it’ll run the code.
- In this way, the cell range C4:C7 will get updated every 5 seconds.
Read More: [Fixed!] Excel Cells Not Updating Unless Double Click (5 Solutions)
Similar Readings
- Refresh All Pivot Tables in Excel (3 Ways)
- How to Auto Refresh Pivot Table in Excel (2 Methods)
- VBA to Refresh Pivot Table in Excel (5 Examples)
- Pivot Table Not Refreshing (5 Issues & Solutions)
- How to Refresh All Pivot Tables with VBA (4 Ways)
3. Automatically Update Excel Worksheet Using VBA
However, we can also auto-update the entire worksheet. Here. we’ll update the 3rd worksheet. Hence, learn the following steps.
STEPS:
- First of all, repeat the steps in example 1 or 2 to get the Module window.
- Now, copy the code and input it there.
Sub UpdateSheet()
Worksheets(3).Calculate
Application.OnTime DateAdd("s", 5, Now), "UpdateSheet"
End Sub
- After saving the file, press F5.
- Accordingly, the 3rd worksheet will get refreshed every 5 seconds.
Read More: How to Auto Refresh Pivot Table without VBA in Excel (3 Smart Methods)
4. Auto Refresh Excel Workbook with Interval 5 Seconds
Finally, if you want to refresh the entire workbook, go through the process below and learn the code.
STEPS:
- In the beginning, get the Module dialog box by repeating the steps in example 1.
- Place the following code in the Module box.
Sub UpdateWorkbook()
Worksheets(1).Calculate
Worksheets(2).Calculate
Worksheets(3).Calculate
Worksheets(4).Calculate
Application.OnTime DateAdd("s", 5, Now), "UpdateWorkbook"
End Sub
- This code will refresh all 4 worksheets you have in the workbook.
Read More: How to Auto Refresh Excel File Without Opening (2 Easy Ways)
Conclusion
Henceforth, you will be able to Auto Update with Interval 5 Seconds in Excel following the above-described examples. Keep using them and let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.
Related Articles
- How to Refresh Chart in Excel (2 Effective Ways)
- Disable Background Refresh in Excel (2 Handy Methods)
- Excel VBA: Turn Off Screen Update
- [Solved]: Excel Formulas Not Updating Until Save (6 Possible Solutions)
- How to Refresh Pivot Table in Excel (4 Effective Ways)
- How to Update a Pivot Table Automatically When Source Data Changes
Hi Aung,
Thanks for the code, could you also indicate a way to stop the update at a given time?
Hi EVAGGELOS,
Thanks for your comment. I am replying on behalf of Exceldemy. Unfortunately, you can’t stop the update at a given time. But you can stop it instantly using a slightly different code and keyboard shortcut. You can follow the steps below for that purpose.
STEPS:
1. Copy and Paste the code in the Module window:
Public RunWhen As Double
Sub UpdateCell()
RunWhen = Now + TimeValue("00:00:05")
Application.OnTime RunWhen, "UpdateCell"
Application.Calculate
End Sub
Sub StopUpdate()
On Error Resume Next
Application.OnTime RunWhen, "UpdateCell", , False
End Sub
2. Press Ctrl + S to save it.
3. Now, press Alt + F8 to open the Macro window.
4. Select StopUpdate from there and then, click on Options. It will open the Macro Options box.
6. In the Macro Options box, type K in the “Shortcut Key” field.
7. Then, click OK to proceed.
8. Now, run the UpdateCell code.
9. To stop updating, press Ctrl + K and the update will be stopped.
I hope this will help you solve your problem. Please let us know if you have any other queries.
Thanks!