First a warning - we use Windows API calls which are very fragile around data typing etc - so if you introduce the slightest hint of a bug, Excel will crash unpleasantly, and you will lose whatever you've done. So save regularly if you are playing around with this stuff. Note I haven't tested the 64bit versions of the code here. I don't even know if it will work. If anyone has 64bit office and you have some results to report, please let me know on the forum. Mac Office will of course not work at all.
This calls back lpTimerFunc after the given amount of milliseconds. The interesting trick we are going to play is to use the nIDEvent to identify which timer it is that we are referring to. Normally you would let setTimer generate its own id, but forcing a specific ID will help us identify the timer later
This is the callback function executed when time is up. Annoyingly, it cannot be a class. It has to be a regular sub in a regular module. However, the nIDEvent is passed to it. We can use that.
The callback will be repeatedly called every interval. To avoid that - and only execute the postponed action once - you have to kill the timer. Usually this would be done in lpTimerFunc
In order to get all this under control and avoid global namespace pollution, we are going to create an instance of a cEventTimer class to manage the mechanics of setTimer. This will raise a custom event on timeout, which will signal other objects that the timer is expired and that it's time to do something. Here's the code. The .start() method kicks off the timer. Note that it uses objPtr(Me) as the nIDEvent. That means that we now have the address of the current instance of the cEventTimer class as the ID of the timer, so when it expires, we will know exactly what has expired. We also pass the address of eventTimer.timerExpire. This is the callback that gets executed on timeout and exists in the eventTimer regular module. Its function will be to execute the .finish() method, which kills this timer, and raises an event saying that we are done. It also passes any data associated with this instance that was set up when the timer was started. Ideally we would like to simply pass the address of .finish() to setTimer - but you can't. Getting over that is what this is all about.
This is the key to making this all work. timerExpire is called back from the timeout, but we cast the nIDEvent as a cEventTimer. Remember that we used objPtr(Me) as the nIDEvent - which is the address of the instance of the cEventTimer class that has just expired. With that information, we can call its .finish() method
All the pieces so far make the reusable content, and should not need modification. Now let's move to consuming it. As a reminder, our objective was to find a way through VBA restrictions so we could
Each of those points has been enabled by the code above. Now let's apply an example. Let's say you have a class, and you want it to be signalled after a period of time - testClass. In its simplest form it would look like this
The key points are
Here's what a calling proc might look like, using your testClass above
One problem with calling an instance of a class in VBA is that it will be garbage collected when the procedure exits. What would happen then is that the expired event would never be signalled - or rather the object that would have received the signal would no longer be there and therfore nothing would happen when the timer expired. That is, unless there is a persistent reference to your class. One way to do this would be to stick Private tClass as testClass at the beginning of your module instead of in your Sub. The problem with that is that you would need to know in advance how many you would need - which kind of defeats part of the objective.
I usually keep a collection of things I would like to stay in memory in a public object. By making a reference to them there, they stick around. Not only that, I also have a central register of what needs to be torn down to recover the memory. It's very simple - just put this code at the beginning of some module. Anything you want to persist, just use
The cDeferredRegister class is in the downloadable workbook associated with Promises in VBA, which is still at the early stages of development. Here is the current version, used with the above
For help and more information join our forum,follow the blog or follow me on twitter .
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation > Classes > Promises in VBA >