VBA Macro On Timer 样式每隔设定的秒数（即 120 秒）运行代码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2319683/
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
VBA Macro On Timer style to run code every set number of seconds, i.e. 120 seconds
提问by FinancialRadDeveloper
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_Openevent 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。我认为使用它是一个坏主意......无论哪种方式？
回答by Alain
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
回答by Todd Main
Yes, you can use Application.OnTimefor 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 StartTimerprocedure in your Auto_Openevent and change what is done in the Timerproceedure (right now it is just updating the time in A1 with ActiveSheet.Cells(1, 1).Value = Time).
您可以将StartTimer程序放在您的Auto_Open事件中并更改程序中所做的事情Timer（现在它只是用 更新 A1 中的时间ActiveSheet.Cells(1, 1).Value = Time）。
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模块中的代码（除此之外），而不是工作表模块。如果您在工作表模块中有它，则代码需要稍作修改。
回答by Alex P
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 afterthe workbook opens then just add a delay of 2 minutes into the Workbook_Openevent
如果您只想在工作簿打开后执行第一段代码，则只需在Workbook_Open事件中添加 2 分钟的延迟
回答by PowerUser
(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 是一个表单级属性。设置后，使用子 Form_Timer 执行您的预期操作。
Sub Form_Load()
Me.TimerInterval = 1000 '1000 = 1 second
End Sub
Sub Form_Timer()
'Do Stuff
End Sub
回答by Julian
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
回答by Gravity Grave
I've found that using OnTimecan 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 Pearsonwas very illuminating. I prefer to use the Windows Timer now, instead of OnTime.
Chip Pearson 的这篇文章非常有启发性。我现在更喜欢使用 Windows 计时器，而不是OnTime.
