Excel VBA Application.OnTime。我认为使用它是一个坏主意......无论哪种方式？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2341762/
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
Excel VBA Application.OnTime. I think its a bad idea to use this... thoughts either way?
提问by FinancialRadDeveloper
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.
一个人希望每 120 秒发生一次事件（请参阅我的另一个问题），另一个人希望在每个工作日下午 5 点发生一件事情。这必须在 Excel 工作表上，因此 VBA 作为插件等将是不可以的，因为它需要是自包含的。
I have a big dislike of using Application.OnTimeI think its dangerous and unreliable, what does everyone else think?
我非常不喜欢使用Application.OnTime我认为它危险且不可靠，其他人怎么看？
EDIT: Cross post is at VBA Macro On Timer style to run code every set number of seconds, i.e. 120 seconds
编辑：Cross post 是在VBA Macro On Timer 样式中每隔设定的秒数运行代码，即 120 秒
回答by Mike Rosenblum
Application.OnTimeis absolutely 100% reliable and is most definitely not dangerous. However, it is onlyexposed 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.
Application.OnTime绝对 100% 可靠，绝对没有危险。但是，它仅通过 VBA 公开，并且出于某种原因，您在此处将 VBA 视为“否”，因此您似乎无法使用此选项。
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.
我一般不会将 OnTime 用于长期调度，例如调度 Excel 每天下午 5 点执行命令。问题是，如果用户关闭 Excel，则 OnTime 计划丢失。在这种情况下，您需要的是使用Task Scheduler，或者创建您自己的应用程序或 Windows 服务来打开 Excel 并执行您的命令。
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.
然而，为了安排一个事件每 120 秒发生一次，使用 Application.OnTime 将是完美的——你只需要重新安排 OnTime 在每次 OnTime 回调时在 120 秒内再次发生，因为 OnTime 只触发一次每个调度，而不是在重复的基础上。我绝对会使用 VBA 来完成这项任务。如果您不希望 VBA 开始该操作，那很好：只需将 VBA 包含在工作簿中，然后由您的程序或通过任务计划程序打开该工作簿。从那时起，VBA 代码可以每 120 秒触发一次。
Make sense?
有道理？
回答by Berry Tsakala
You're right. an "infinite" interval of "onTime" calling itself, creates an infinite recursion.
你是对的。“onTime”调用自身的“无限”间隔会创建无限递归。
It willcause a stack overflow after few thousand/million/billion function calls, and it will "leak" memory.
它会在几千/百万/十亿次函数调用后导致堆栈溢出，并且会“泄漏”内存。
