Excel：每x秒重新计算一次 [英] Excel: Recalculating every x seconds

问题描述

我的一个电子表格处理各种计算，其中包括当前的日期和时间，并且很快将其自动刷新一次，而不是手动按F9或更改其中一个单元格。

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.

Excel中有没有办法设置一个电子表格，以便每x秒自动重新计算一次？

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

我没有能够在Excel中找到一个设置，也许表示没有这样的功能存在。如果没有，可以用VBA来实现吗？ （后者可能听起来可能不是一个愚蠢的问题，但是我以前没有经验编写Excel宏，因此不知道它的功能是如何操纵电子表格的。）

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

推荐答案

此代码将创建一个时钟，每10秒更新一次。

请注意，它仅刷新特定的单元格，而不是整个工作簿 - 这意味着您可以随时随地离开计算选项：

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

并确保它在此工作簿模块中：

Private Sub Workbook_BeforeClose(Cancel As Boolean)
EndTime
End Sub

这篇关于Excel：每x秒重新计算一次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！