5

One of my spreadsheets deals with various calculations involving, among other things, the current date and time and it would be nice to have it automatically refresh itself once in a while instead of manually having to either press F9 or altering one of the cells.

Is there some way in Excel to set a spreadsheet to automatically recalculate itself every x seconds?

I haven't been able to find a setting in Excel itself, perhaps indicating that no such feature exists. If not, can this be achieved with VBA? (The latter may or may not sound like a silly question, but I have no prior experience with writing Excel macros and as such have no idea what its capabilities are in terms of manipulating spreadsheets.)

lfrandom
  • 1,013
  • 2
  • 10
  • 32
BambooleanLogic
  • 7,530
  • 3
  • 30
  • 56
  • Best solution is probably to set it to recalculate when the user interacts with it in some way, for example with the Worksheet_SelectionChange event. – mattboy Jul 29 '13 at 12:52

2 Answers2

9

This code will create a clock, updated every 10 seconds.
Note that it only refreshes specific cells, and not the entire workbook - this means that you can leave the calculation options at whatever you are happy with:

Dim SchedRecalc As Date

Sub Recalc()
'Change specific cells
Range("A1").Value = Format(Now, "dd-mmm-yy")
Range("A2").Value = Format(Time, "hh:mm:ss AM/PM")
'or use the following line if you have a cell you wish to update
Range("A3").Calculate

Call StartTime ' need to keep calling the timer, as the ontime only runs once
End Sub

Sub StartTime()
SchedRecalc = Now + TimeValue("00:00:10")
Application.OnTime SchedRecalc, "Recalc"
End Sub

Sub EndTime()
On Error Resume Next
Application.OnTime EarliestTime:=SchedRecalc, _
        Procedure:="Recalc", Schedule:=False
End Sub

and, to make sure it stops, in the This Workbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
EndTime
End Sub
SeanC
  • 15,695
  • 5
  • 45
  • 66
  • Does `StartTime` need to be called from `Worksheet.Activate` or something? –  Apr 20 '16 at 20:36
  • @Devil'sAdvocate, it can be called from any routine - you can bind the start and stop to a hotkey, for instance. You do need to call StartTime from somewhere to get it to loop – SeanC Apr 20 '16 at 22:10
  • This solution does not work at all if you open another workbook. It starts updating cells in that workbook and writes dates and times to that workbook. (However, this is Excel 2016 on a Mac, so it can be a Mac/2016 issue...) On the other hand, working in another workbook seems to update all workbooks... um. – Erk Dec 08 '16 at 15:14
  • @Erk, the range is not keyed to a specific workbook or worksheet. You can extend the recalculation to specify the appropriate place to avoid this spilling onto sheets you don't want to be changed – SeanC Dec 08 '16 at 15:24
  • @SeanC, Yep. I noticed. I'm now using `Workbooks("MyFile.xlsb").Sheets("Sheet1").Calculate`. I guess I might have been able to go with `Workbooks("MyFile.xlsb").Calculate` but I haven't tested that one! – Erk Dec 09 '16 at 17:42
  • Nope `Workbooks("MyFile.xlsb").Calculate` does not work. Seems Workbooks don't have `Calculate`... – Erk Dec 09 '16 at 17:48
2

Goto Developer Visual basic Editor - Right Click workbook - insert module (make sure you have manual calculation

in the module

Sub run_over
Timetorun = Now + timevalue("00:00:10")
application.ontime timetorun,"Refresh_all"
End Sub

Sub Refresh_all
Activeworkbook.Refreshall
End Sub

Sub auto_close()
Application.OnTime timetorun, Refresh_all, , False
End Sub

Change the timing in "00:00:00" format as required

Vasim
  • 3,052
  • 3
  • 35
  • 56
  • 1
    But will "ActiveWorkbook" really do what you want if you're woking on other excel sheets while having this one running? It seems to me it's the "window on top"? – Erk Dec 08 '16 at 14:46