Excel VBA 应用程序.OnTime。我认为使用这个……想法是个坏主意？
我支持的许多用户要求事情自动发生（当然更自动，但这是另一点！）。
一个希望事件每 120 秒发生一次（请参阅我的其他问题），另一个希望在每个工作日下午 5 点发生一件事。这必须在 Excel 工作表上，因此 VBA 作为插件等将是不行的，因为它需要是独立的。
我非常不喜欢使用 Application.OnTime 我认为它危险且不可靠，其他人怎么看？
编辑： 交叉帖子位于 VBA 宏定时器样式每隔设定的秒数（即 120 秒）运行代码
I have a number of users I support that are asking for things to happen automatically ( well more automagically but that's another point!).
One want events to happen every 120 secs ( see my other question ) and also another wants 1 thing to happen say at 5pm each business day. This has to be on the Excel sheet so therefore VBA as addins etc will be a no no, as it needs to be self contained.
I have a big dislike of using Application.OnTime I think its dangerous and unreliable, what does everyone else think?
EDIT:
Cross post is at VBA Macro On Timer style to run code every set number of seconds, i.e. 120 seconds
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
Application.OnTime 绝对 100% 可靠，并且绝对不危险。但是，它仅通过 VBA 公开，并且出于某种原因，您将 VBA 视为“不”，因此此选项对您来说似乎不可用。
我一般不会使用 OnTime 进行长期调度，例如安排 Excel 每天下午 5 点执行命令。问题是，如果用户关闭 Excel，则 OnTime 调度就会丢失。在这种情况下，您需要使用 任务Scheduler，或创建您自己的应用程序或 Windows 服务来打开 Excel 并执行您的命令。
然而，为了安排一个事件每 120 秒发生一次，使用 Application.OnTime 将是完美的——每次 OnTime 回调时，您只需要重新安排 OnTime 在 120 秒内再次发生，因为 OnTime 仅触发一次根据安排，而不是重复。我绝对会使用 VBA 来完成这项任务。如果您不希望 VBA 开始执行操作，也可以：只需将 VBA 包含在工作簿中，然后由您的程序或通过任务计划程序打开该工作簿。从那时起，VBA 代码可以每 120 秒触发一次。
有道理吗？
Application.OnTime is absolutely 100% reliable and is most definitely not dangerous. However, it is only exposed via VBA and you are regarding VBA as a "no no" for some reason here, so this option would appear to be unavailable to you.
I would generally not use OnTime for long-term scheduling, such as scheduling Excel to execute a command each day at 5pm. The problem is that if the user closes Excel, then the OnTime scheduling is lost. What you would need, in this case, is to use the Task Scheduler, or create your own application or windows service to open Excel and execute your commands.
For scheduling an event to occur every 120 seconds, however, using Application.OnTime would be perfect for this -- you would simply need to re-schedule OnTime to occur again in 120 seconds each time that OnTime calls back, because OnTime only fires once per scheduling, not on a repeat basis. I would absolutely use VBA for this task. If you don't want VBA commencing the action, that is fine: just have the VBA contained in a workbook which is then opened by your program or via the Task Scheduler. From that point onward, the VBA code can fire every 120 seconds.
Make sense?
你说得对。 “onTime”调用自身的“无限”间隔会创建无限递归。
在几千/百万/十亿次函数调用后，它会导致堆栈溢出，并且会“泄漏”内存。
You're right. an "infinite" interval of "onTime" calling itself, creates an infinite recursion.
It will cause a stack overflow after few thousand/million/billion function calls, and it will "leak" memory.