Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Auto Update with Interval 5 Seconds in Excel

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.

excel auto update interval 5 seconds

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.

Auto Update Cell Every 5 Seconds with Excel VBA

  • 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

Apply VBA Code to Refresh Cell Range with Interval 5 Seconds

  • 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


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

Automatically Update Excel Worksheet Using VBA

  • 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

Auto Refresh Excel Workbook with Interval 5 Seconds

  • This code will refresh all 4 worksheets you have in the workbook.

Read More: How to Update a Pivot Table Automatically When Source Data Changes


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

Aung

Aung

My name is Aung. I have my B.Sc. degree in EEE. From now on, I will be working with Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

2 Comments
  1. Reply
    Evaggelos Anastasiadis Sep 23, 2022 at 1:18 PM

    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!

Leave a reply

ExcelDemy
Logo