Excel VBA Application.OnTime. I think its a bad idea to use this... thoughts either way?

Question

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

Gan

Answer 1

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?

Lesbian

Isn't "onTime calling to itself creating an infinite recursion? Wouldn't it cause a stack overflow after few thousand/million/billion function calls? – Leyte

Answer 2

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.

Leyte

This isn't correct. A method calling OnTime does not place the call on the stack, so a stack overflow can never occur. A resource leak in VBA is difficult to achieve, even intentionally, as it automatically reclaims memory via reference counting. Custom classes with circular object references could do it, but most VBA code does not even use classes. An OnTime loop, even an infinite one, will not cause a leak. – Lesbian

How do you know? Are you an MS developer? it's VB6 we're talking about; it's practically a pile of bugs that runs on a whim and single-process RND()s at its core – Leyte