MS Excel - How to refresh a cell automatically every 5 seconds?

Question

I have a cell in my Excel sheet and I need this cell to be refreshed every 5 seconds.

MS Excel itself enables to set refreshing but it is only every 1 minute and that is insufficiently too long..

I need some code in VBA or macro set to be run every 5 seconds and do exactly what would be done if I clicked Refresh every 5 seconds as it is shown here:

Somehow I didn't find any example of that I have absolutely no clue how to do that as I don't know how to work in VBA..

Answer 1

You can use Application.OnTime to schedule the auto refresh. Do create the below VBA subroutine and execute. It will auto calculate cell A1 every 5 seconds.

Sub UpdateCell()
    ActiveWorkbook.RefreshAll
    Application.OnTime Now + TimeValue("00:00:5"), "UpdateCell"
End Sub

Answer 2

Got it, now it works:

Sub UpdateCell()
    ActiveWorkbook.RefreshAll
    Application.OnTime Now + TimeValue("00:00:5"), "List1.UpdateCell"
End Sub