VBA宏计时器样式每设定的秒数即120秒运行代码 [英] VBA Macro On Timer style to run code every set number of seconds, i.e. 120 seconds

问题描述

我需要每120秒运行一段代码。我正在寻找一个简单的方法来做到这一点在VBA。我知道可以从 Auto_Open 事件中获取计时器值，以防止使用魔术数字，但是我不能完全了解定时器让每隔120秒运行一次。

如果我可以避免，我真的不想使用带有睡眠的无限循环。

---

编辑：

根据提供的答案进行跨职位是：Excel VBA Application.OnTime。我认为使用这个想法是一个坏主意吗？

解决方案

当工作簿首次打开，执行此代码：

  alertTime = Now + TimeValue（00:02:00）
 Application.OnTime alertTime，EventMacro
  

然后在工作簿中有一个称为EventMacro的宏，重复它

  Public Sub EventMacro（）
'...在这里执行您的操作
 alertTime = Now + TimeValue（00:02:00）
 Application.OnTime alertTimeEventMacro
 End Sub 
  

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.

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?

解决方案

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.

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

这篇关于VBA宏计时器样式每设定的秒数即120秒运行代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！