VBA宏在定时器样式上运行每一秒的代码，即120秒

I have a need to run a piece of code every 120 seconds. I am looking for an easy way to do this in VBA. I know that it would be possible to get the timer value from the Auto_Open event to prevent having to use a magic number, but I can't quite get how to fire off a timer to get something to run every 120 seconds.

我需要每120秒运行一段代码。我正在寻找一个简单的方法来做VBA。我知道可以从Auto_Open事件获取计时器值，以避免使用一个神奇的数字，但我不太清楚如何触发一个计时器，以便每120秒运行一次。

I don't really want to use an infinite loop with a sleep if I can avoid it.

如果可以避免的话，我不想在睡眠中使用无限循环。

EDIT:

编辑:

Cross-post based on an answer provided is at: Excel VBA Application.OnTime. I think its a bad idea to use this... thoughts either way?

基于所提供的答案的交叉张贴是:Excel VBA Application.OnTime。我认为使用这个是一个坏主意。想法无论哪种方式?

6 个解决方案

#1

When the workbook first opens, execute this code:

当工作簿第一次打开时，执行以下代码:

alertTime = Now + TimeValue("00:02:00")
Application.OnTime alertTime, "EventMacro"

Then just have a macro in the workbook called "EventMacro" that will repeat it.

然后在工作簿中设置一个名为“EventMacro”的宏，它将重复这个操作。

Public Sub EventMacro()
    '... Execute your actions here'
    alertTime = Now + TimeValue("00:02:00")
    Application.OnTime alertTime, "EventMacro"
End Sub

#2

Yes, you can use Application.OnTime for this and then put it in a loop. It's sort of like an alarm clock where you keep hittig the snooze button for when you want it to ring again. The following updates Cell A1 every three seconds with the time.

是的，你可以用Application。OnTime为此，然后将它放入一个循环中。它有点像一个闹钟，当你想让它再次响起的时候，你就按下止闹按钮。以下内容每三秒更新一次A1单元格。

Dim TimerActive As Boolean
Sub StartTimer()
    Start_Timer
End Sub

Private Sub Start_Timer()
    TimerActive = True
    Application.OnTime Now() + TimeValue("00:00:03"), "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:00:03"), "Timer"
    End If
End Sub

You can put the StartTimer procedure in your Auto_Open event and change what is done in the Timer proceedure (right now it is just updating the time in A1 with ActiveSheet.Cells(1, 1).Value = Time).

您可以将StartTimer过程放在Auto_Open事件中，并更改在Timer过程中执行的操作(现在它只是用ActiveSheet更新A1中的时间)。细胞(1,1)value =时间)。

Note: you'll want the code (besides StartTimer) in a module, not a worksheet module. If you have it in a worksheet module, the code requires slight modification.

注意:您将希望代码(除了StartTimer)在模块中，而不是工作表模块中。如果在工作表模块中有，则需要对代码进行轻微修改。

#3

In Workbook events:

在工作簿事件:

Private Sub Workbook_Open()
    RunEveryTwoMinutes
End Sub

In a module:

在一个模块:

Sub RunEveryTwoMinutes()
    //Add code here for whatever you want to happen
    Application.OnTime Now + TimeValue("00:02:00"), "RunEveryTwoMinutes"
End Sub

If you only want the first piece of code to execute after the workbook opens then just add a delay of 2 minutes into the Workbook_Open event

如果您只希望在工作簿打开后执行第一部分代码，那么只需在Workbook_Open事件中添加2分钟的延迟

#4

(This is paraphrased from the MS Access help files. I'm sure XL has something similar.) Basically, TimerInterval is a form-level property. Once set, use the sub Form_Timer to carry out your intended action.

(这是来自MS Access帮助文件的改写。我肯定XL也有类似的东西。基本上，TimerInterval是一个表单级属性。设置好后，使用sub Form_Timer来执行您的预期操作。

Sub Form_Load()
    Me.TimerInterval = 1000 '1000 = 1 second
End Sub

Sub Form_Timer()
    'Do Stuff
End Sub

#5

I've found that using OnTime can be painful, particularly when:

我发现使用OnTime会很痛苦，尤其是当:

You're trying to code and the focus on the window gets interrupted every time the event triggers.
您正在尝试编写代码，每次事件触发时，窗口的焦点都会被中断。
You have multiple workbooks open, you close the one that's supposed to use the timer, and it keeps triggering and reopening the workbook (if you forgot to kill the event properly).
您打开了多个工作簿，您关闭了应该使用计时器的工作簿，它继续触发并重新打开工作簿(如果您忘记正确地终止事件)。

This article by Chip Pearson was very illuminating. I prefer to use the Windows Timer now, instead of OnTime.

皮尔森的这篇文章很有启发性。我更喜欢现在使用Windows定时器，而不是OnTime。

#6

My solution:

我的解决方案:

Option Explicit
Public datHora As Date

Function Cronometro(action As Integer) As Integer 
'This return the seconds between two >calls
Cronometro = 0
  If action = 1 Then 'Start
    datHora = Now
  End If
  If action = 2 Then 'Time until that moment
    Cronometro = DateDiff("s", datHora, Now)
  End If
End Function

How to use? Easy...

如何使用?容易……

dummy= Cronometro(1) ' This starts the timer

seconds= Cronometro(2) ' This returns the seconds between the first call and this one

#1