vba 如何在 Excel 中显示正在运行的时钟？

How do I show a running clock in Excel?

提问by phan

I'd like to show a clock in cell A1 of Excel 2007. I'm familiar with NOW() and TODAY() but it doesn't refresh itself every 1 minute like I want it to. You know, like a running clock. I just want the current time in h:mm to be in cell A1. Is this possible?

我想在 Excel 2007 的单元格 A1 中显示一个时钟。我熟悉 NOW() 和 TODAY()，但它不会像我希望的那样每 1 分钟刷新一次。你知道，就像一个正在运行的时钟。我只希望 h:mm 中的当前时间在单元格 A1 中。这可能吗？

From this clock I will do further calculations like How long has it been since I last did Activity X, Y, and Z. Thanks SO.

从这个时钟开始，我将做进一步的计算，比如我上次做活动 X、Y 和 Z 已经多久了。谢谢。

回答by Siddharth Rout

Found the codethat I referred to in my comment above. To test it, do this:

找到了我在上面的评论中提到的代码。要测试它，请执行以下操作：

1. In Sheet1change the cell height and width of say A1as shown in the snapshot below.
2. Format the cell by right clicking on it to show time format
3. Add two buttons (form controls) on the worksheet and name them as shown in the snapshot
4. Paste this code in a module
5. Right click on the Start Timerbutton on the sheet and click on Assign Macros. Select StartTimermacro.
6. Right click on the End Timerbutton on the sheet and click on Assign Macros. Select EndTimermacro.

1. 在Sheet1更改 say 的单元格高度和宽度时，A1如下面的快照所示。
2. 通过右键单击单元格来格式化单元格以显示时间格式
3. 在工作表上添加两个按钮（表单控件）并将它们命名为快照所示
4. 将此代码粘贴到模块中
5. 右键单击工作表上的Start Timer按钮，然后单击Assign Macros。选择StartTimer宏。
6. 右键单击工作表上的End Timer按钮，然后单击Assign Macros。选择EndTimer宏。

Now click on Start Timerbutton and you will see the time getting updated in cell A1. To stop time updates, Click on End Timerbutton.

现在单击Start Timer按钮，您将看到单元格中的时间更新A1。要停止时间更新，请单击“结束计时器”按钮。

Code (TRIED AND TESTED)

代码（久经考验）

Public Declare Function SetTimer Lib "user32" ( _
ByVal HWnd As Long, ByVal nIDEvent As Long, _
ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long

Public Declare Function KillTimer Lib "user32" ( _
ByVal HWnd As Long, ByVal nIDEvent As Long) As Long

Public TimerID As Long, TimerSeconds As Single, tim As Boolean
Dim Counter As Long

'~~> Start Timer
Sub StartTimer()
    '~~ Set the timer for 1 second
    TimerSeconds = 1
    TimerID = SetTimer(0&, 0&, TimerSeconds * 1000&, AddressOf TimerProc)
End Sub

'~~> End Timer
Sub EndTimer()
    On Error Resume Next
    KillTimer 0&, TimerID
End Sub

Sub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, _
ByVal nIDEvent As Long, ByVal dwTimer As Long)
    '~~> Update value in Sheet 1
    Sheet1.Range("A1").Value = Time
End Sub

SNAPSHOT

快照

回答by StoriKnow

See the below code (taken from this post)

请参阅下面的代码（取自这篇文章）

Put this code in a Module in VBA (Developer Tab -> Visual Basic)

将此代码放在 VBA 中的模块中（开发人员选项卡 - > Visual Basic）

Dim TimerActive As Boolean
Sub StartTimer()
    Start_Timer
End Sub
Private Sub Start_Timer()
    TimerActive = True
    Application.OnTime Now() + TimeValue("00:01:00"), "Timer"
End Sub
Private Sub Stop_Timer()
    TimerActive = False
End Sub
Private Sub Timer()
    If TimerActive Then
        ActiveSheet.Cells(1, 1).Value = Time
        Application.OnTime Now() + TimeValue("00:01:00"), "Timer"
    End If
End Sub

You can invoke the "StartTimer" function when the workbook opens and have it repeat every minute by adding the below code to your workbooks Visual Basic "This.Workbook" class in the Visual Basic editor.

您可以在工作簿打开时调用“StartTimer”函数，并通过将以下代码添加到您的工作簿 Visual Basic 编辑器中的 Visual Basic“This.Workbook”类，让它每分钟重复一次。

Private Sub Workbook_Open()
    Module1.StartTimer
End Sub

Now, every time 1 minute passes the Timerprocedure will be invoked, and set cell A1 equal to the current time.

现在，每过 1 分钟就会调用Timer过程，并将单元格 A1 设置为等于当前时间。