vba Excel：每 x 秒重新计算一次
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17924542/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel: Recalculating every x seconds
提问by Smallhacker
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.
我的一个电子表格处理各种计算，其中包括当前日期和时间，最好让它不时自动刷新，而不是手动按 F9 或更改其中一个单元格。
Is there some way in Excel to set a spreadsheet to automatically recalculate itself every x seconds?
Excel 中是否有某种方法可以将电子表格设置为每 x 秒自动重新计算一次？
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.)
我一直无法在 Excel 本身中找到设置，这可能表明不存在此类功能。如果没有，这可以用 VBA 实现吗？（后者可能听起来像也可能不是一个愚蠢的问题，但我之前没有编写 Excel 宏的经验，因此不知道它在操作电子表格方面的功能是什么。）
回答by SeanC
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:
此代码将创建一个时钟，每 10 秒更新一次。
请注意，它仅刷新特定单元格，而不是整个工作簿 - 这意味着您可以将计算选项保留在您满意的任何位置：
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 Workbookmodule:
并且，为了确保它停止，在本工作簿模块中：
Private Sub Workbook_BeforeClose(Cancel As Boolean)
EndTime
End Sub
回答by Vasim
Goto Developer Visual basic Editor - Right Click workbook - insert module (make sure you have manual calculation
转到开发人员 Visual Basic 编辑器 - 右键单击工作簿 - 插入模块（确保您有手动计算
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
根据需要更改“00:00:00”格式的时间
