CODEWITHSUNDEEP
excelrefreshsecondsvba
user3684090
user3684090

Reputation: 11

How to refresh Excel file every second?

I have a list of stock prices pulled from Google finance and placed in different sheets in my Excel. I'm wondering, Can I refresh Excel sheet every SECOND (not minute) according to the Google finance stock price?

Upvotes: 1

Views: 22740

Answers (2)

Byron Wall
Byron Wall

Reputation: 4010

This can be done without having a macro constantly running. It relies on the Application.OnTime method which allows an action to be scheduled out in the future. I have used this approach to force Excel to refresh data from external sources.

The code below is based nearly exclusively on the code at this link: http://www.cpearson.com/excel/ontime.aspx

The reference for Application.OnTime is at: https://msdn.microsoft.com/en-us/library/office/ff196165.aspx

Dim RunWhen As Date

Sub StartTimer()
    Dim secondsBetween As Integer
    secondsBetween = 1

    RunWhen = Now + TimeSerial(0, 0, secondsBetween)
    Application.OnTime EarliestTime:=RunWhen, Procedure:="CodeToRun", Schedule:=True
End Sub

Sub StopTimer()
    On Error Resume Next
    Application.OnTime EarliestTime:=RunWhen, Procedure:="CodeToRun", Schedule:=False
End Sub

Sub EntryPoint()

    'you can add other code here to determine when to start
    StartTimer

End Sub

Sub CodeToRun()

    'this is the "action" part
    [A1] = WorksheetFunction.RandBetween(0, 100)


    'be sure to call the start again if you want it to repeat
    StartTimer

End Sub

In this code, the StartTimer and StopTimer calls are used to manage the Timers. The EntryPoint code gets things started and CodeToRun includes the actual code to run. Note that to make it repeat, you call StartTimer within CodeToRun. This allows it to loop. You can stop the loop by calling the StopTimer or simply not calling StartTimer again. This can be done with some logic in CodeToRun.

I am simply putting a random number in A1 so that you can see it update.

Upvotes: 2

tea_pea
tea_pea

Reputation: 1542

Sub RefreshFormulasEverySecond()

    Dim dtTargetTime As Date

    Debug.Print "Started"

    Do While Range("A1").Value <> "STOP"
        Application.Calculate
        dtTargetTime = Now + TimeValue("0:00:01")
        Do While Now < dtTargetTime
            DoEvents
        Loop
        Debug.Print Now
    Loop

    Debug.Print "Stopped"

End Sub

You could have this macro running in the background. Paste it into a VBA module. You can run it from there or else put a button on the sheet and use that to trigger it. It's written to stop running when the word "STOP" is typed in cell A1 of whatever sheet the user is looking at.

I'm not sure it's the greatest idea to have a macro running continuously in the background, but that was the only way I could think of.

Upvotes: 0

Related Questions