Excel VBA Application.OnTime。我认为这是一个坏主意吗？ [英] Excel VBA Application.OnTime. I think its a bad idea to use this... thoughts either way?

问题描述

我有一些我支持的用户要求自动发生的事情（更多的是自动化，但这又是另一回事）。

每120秒（见我的另一个问题），另外还有一件事要在每个工作日下午5点发生。这必须在Excel表上，因此VBA作为加载项等将是一个不，否定，因为它需要自包含。

我非常不喜欢使用 Application.OnTime 我认为其危险和不可靠，其他人都认为？

---

编辑：
十字架位于 VBA Macro On Timer样式每设定秒数运行代码，即120秒

解决方案

Application.OnTime 绝对是100％可靠的，绝对不是危险的。但是，仅通过VBA公开 ，您将VBA视为否，因为某些原因在此，因此此选项似乎无法使用。

我通常不会使用OnTime进行长期调度，例如安排Excel每天在下午5点执行一个命令。问题是如果用户关闭Excel，则OnTime调度将丢失。在这种情况下，您需要使用的是任务计划程序，或创建自己的应用程序或Windows服务以打开Excel并执行您的命令。

但是，为了计划每120秒发生一次事件，使用Application.OnTime将是完美的 - 您只需要重新安排OnTime再次在120秒内再次发生OnTime回调，因为OnTime只会每次调度一次，而不是重复。我绝对会使用VBA来完成这个任务。如果您不希望VBA开始动作，那就行了，只需将VBA包含在工作簿中，然后由程序或任务计划程序打开。从那以后，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

解决方案

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?

这篇关于Excel VBA Application.OnTime。我认为这是一个坏主意吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！