Auto Refresh Excel Every 1 Second Using VBA in Excel

In this article, you will learn how to auto refresh excel file every 1 second using VBA code.

Question): In column A there are some values stored & I want them to automatically refresh every 1 second.

To refresh excel file every 1 second we will follow the below steps:

  • In Range A1:A10; use RAND function
  • We need to follow the below steps to launch VB editor
  • Click on Developer tab
  • From Code group select Visual Basic

img1

 

  • Click on Insert then Module

img2

 

This will create new module.

Enter the following code in the Module

Sub Calculate_Range()

 Range("A1:A5").Calculate

 Application.OnTime DateAdd("s", 1, Now), "Calculate_Range"

End Sub
img3

 

  • The above code has used the code application.ontime which is used to auto calculate the range A1:A5 to refresh every 1 second.
  • Range A1:A5 contains RAND function which will keep on updating after every 1 second

img4

 

In this way we can schedule the excel file to refresh every x amount of time & save.

Users are saying about us...

  1. When I close the workbook that contains this VBA, it reopens automatically and according to the refresh interval stipulated. Only happens if I have another workbook open, as obviously that stops Excel from closing completely.

    • You can use OnTime events to refresh excel every 1 second.
      sub RecalculateSheet()
      application.calculate
      end sub

      sub calculculateEvery1Sec()
      Application.ontime Now + (1 / 24 / 60 / 60), "RecalculateSheet"
      end sub

      Here is the brief and explanatory article about events in excel: Events in Excel VBA .

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube