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.)

Improve this question
1
  • 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. Commented Jul 29, 2013 at 12:52

2 Answers 2

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Does StartTime need to be called from Worksheet.Activate or something?
@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
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, 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, 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!
|
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

Improve this answer

1 Comment

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"?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.