and no, I'm not talking about
Workbook_BeforeClose. Note that the motivation of this question is Excel specific but the technique I'd like feedback on is not.
If you don't care about the motivation, you can skip to the chapter Option 3, the "magic" way.
If additionally, you already know about the
IUnknown::Release trick, you can skip to the chapter Implementation using
I recently came across the following question on StackOverflow: How can i get the NOW function to update every second?
Immediately, I thought about
Application.OnTime and implemented a solution using this technique. I will not post the original code here as it is not what this question is about, but rather what motivated it. A simplified example of the code looks like this:
Sub KeepRecalculating() Sheet1.Range("A1").Calculate Application.OnTime DateAdd("s", 1, Now()), "KeepRecalculating" End Sub
In principle, this solution works well and there are quite a few other questions on StackOverflow with answers that adhere to the same idea. Unfortunately, there two subtle and quite annoying drawbacks to this technique that originate in some rather interesting features of
Both of these issues have to do with how to cancel a task scheduled with
- Enabling the solution to stop the recursive updating.
This is still quite straight forward. We need to save the planned next execution time and call
Application.OnTime again with the additional parameter
schedule:=False. The following code implements an additional parameter that enables the update loop to be stopped manually.
Sub KeepRecalculating(Optional ByVal schedule As Boolean = True) Static nextExec As Double Sheet1.Range("A1").Calculate If schedule Then nextExec = DateAdd("s", 1, Now()) Application.OnTime nextExec, "KeepRecalculating", , schedule End Sub
My solution also took parameters like
updateRate and was able to run multiple tasks simultaneously independent of each other to make it more generally applicable.
There is still the annoying fact that with this solution, pressing the
Stop button in the VBA IDE does not stop the loop from running. A specific sub has to be called every time to stop it.
The next issue, however, is even more annoying. What if the user just closes the workbook expecting it to also stop updating? Well in this case
Application.OnTime has a trick up its sleeve that can be a very useful feature in other situations.
Application.OnTimewill reopen a closed Workbook if a macro was scheduled to run in it!
There seem to be two options to deal with this:
- The user stops the loop manually before closing. This is very inconvenient and not really viable for most people.
- The user adds code to the
Workbook_BeforeCloseevent. This is a more reasonable option and the one I recommended in my answer on StackOverflow. It comes with the disadvantage of adding code to the
ThisWorkbookmodule which is unsatisfying regarding encapsulation. It would be nice to have everything in one place.
The best option seems to be 2), yet this still comes with some drawbacks.
Stop button in the VBA IDE still can't interrupt the loop, and secondly, if the user for some reason had set
Application.EnableEvents = False, the
Workbook_BeforeClose event would never be called and the loop would have to be interrupted manually.
While both of these are not huge issues, it would still be nice to overcome them. When I asked @CristianBuse about this, I got a hint that opened the door to some hacking, making all of this possible.
Option 3, the "magic" way
I'm talking about the
The code to produce all of this magical behavior looks like this:
#If Mac Then Private Declare PtrSafe Function CopyMemory Lib "/usr/lib/libc.dylib" Alias "memmove" (Destination As Any, Source As Any, ByVal Length As LongPtr) As LongPtr #Else Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As LongPtr) #End If Public Sub Release(ByVal instancePtr As LongPtr) MsgBox "Magic!" End Sub 'If the Sub Main gets executed, Release will be called once the object containing 'this code gets terminated or the VBA environment is reset (e.g. Stop button) Sub Main() Static o As IUnknown Static vtbl(0 To 2) As LongPtr, vtblPtr As LongPtr If vtblPtr = 0 Then vtbl(2) = VBA.Int(AddressOf Release) vtblPtr = VarPtr(vtbl(0)) CopyMemory ByVal VarPtr(o), VarPtr(vtblPtr), LenB(vtblPtr) End If End Sub
This code works by creating a "fake" object of type
IUnknown and hooking into its
A static variable of type
IUnknown is declared and a fake virtual method table is inserted. The virtual method table can point to three methods:
IUnknown::AddRef, called whenever the instances reference counter is incremented
IUnknown::QueryInterface, called whenever information about the object's interface is required
IUnknown::Release, called whenever the instances reference counter is decremented
For the above-described problem, the
Release method seems very, very interesting. Once
Main was called,
Release will be called, whenever the object
o's reference count gets decremented. Because o is static and we don't create other instances, this happens in three cases:
- When the function containing it gets recompiled. (Excel will crash)
- When the
Stopbutton in the VBA-IDE is pressed.
- When the Workbook is closed.
The two latter cases are exactly what we need! The first one is not really a problem as long as the function isn't touched while
o is active.
Implementation of the original problem using
The obvious (and probably best) way to implement this is as follows:
#If Mac Then Private Declare PtrSafe Function CopyMemory Lib "/usr/lib/libc.dylib" Alias "memmove" (Destination As Any, Source As Any, ByVal Length As LongPtr) As LongPtr #Else Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As LongPtr) #End If Public Sub Release(ByVal instancePtr As LongPtr) KeepRecalculating schedule:=False End Sub Sub SetIUnknown() Static isSet As Boolean: If isSet Then Exit Sub Static o As IUnknown Static vtbl(0 To 2) As LongPtr, vtblPtr As LongPtr If vtblPtr = 0 Then vtbl(2) = VBA.Int(AddressOf Release) vtblPtr = VarPtr(vtbl(0)) CopyMemory ByVal VarPtr(o), VarPtr(vtblPtr), LenB(vtblPtr) End If isSet = True End Sub Sub KeepRecalculating(Optional ByVal schedule As Boolean = True) Static nextExec As Double SetIUnknown Sheet1.Range("A1").Calculate If schedule Then nextExec = DateAdd("s", 1, Now()) Application.OnTime nextExec, "KeepRecalculating", , schedule End Sub
This works beautifully. Clicking
Stop in the IDE stops the loop, closing the Workbook stops the loop, even if
Application.EnableEvents = False. This trick is not application specific, just a little bit hacky. In the above example, only
Range.Calculate are Excel-specific. The trick could also be useful in other contexts in other applications.
My first question is:
Is this approach viable? It's a bit hacky, for example placing a breakpoint inside the
Release method or anything called through it crashes the Application. This would be no problem in "production", are there other issues? I can't see any.
Venturing further into the possibilities
Bad habits led me to perform further experiments on the limits of this method.
Because of the VBA-IDEs inability to organize larger amounts of modules, I sometimes seek to implement certain features in a single procedure.
I like the portability of having one blob of code without dependencies, which I can just copy into a module and use. This way, the amount of modules in actual projects stays small, and navigating the code is easier.
Knowing about the drawbacks of this habit, but fueled by a certain curiosity I wondered if it was possible to implement all of the above-described functionality in a single procedure.
As it turned out, yes it is.
I will not post the entire subroutine here, as it contains a lot of irrelevant fluff. For those interested, it can be found in this Gist. If you insist on critiquing it I won't object since this is CodeReview but please know that I'm well aware of its terrible style and utter unreadability in its current form. My goal was to create a magic blob of code, its usefulness was questionable from the beginning.
Sub showcases the essence of the technique.
#If Mac Then Private Declare PtrSafe Function CopyMemory Lib "/usr/lib/libc.dylib" Alias "memmove" (Destination As Any, Source As Any, ByVal Length As LongPtr) As LongPtr #Else Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As LongPtr) #End If Public Sub KeepRecalculating(Optional ByRef Range As Object = Nothing, _ Optional ByRef refreshTimeSeconds As Long = 1&, _ Optional ByRef schedule As Boolean = True, _ Optional ByRef stopAll As Boolean = False) Static o As IUnknown '<--- this MUST be the first static variable allocated! Static vtbl(0 To 2) As LongPtr, vtblPtr As LongPtr If vtblPtr = 0 Then vtbl(2) = VBA.Int(AddressOf KeepRecalculating) vtblPtr = VarPtr(vtbl(0)) CopyMemory ByVal VarPtr(o), VarPtr(vtblPtr), LenB(vtblPtr) End If Static nextExec As Double, isSet As Boolean #If Win64 Then 'Detect if sub was called as "Release": If VarPtr(o) = VarPtr(schedule) Then #Else If VarPtr(o) = VarPtr(stopAll) Then #End If If Not isSet Then Exit Sub Application.OnTime nextExec, "KeepRecalculating", , False Exit Sub 'Exit Sub if called as "Release" to prevent accessing of parameters End If 'The Subs parameters MUST NOT be accessed before this line! Sheet1.Range("A1").Calculate If schedule Then nextExec = DateAdd("s", 1, Now()) Application.OnTime nextExec, "KeepRecalculating", , schedule isSet = True End Sub
You may notice that this function has a bunch of parameters that go practically unused. In my original solution, I need all of these parameters and more, here they are just necessary to showcase the method.
Safely implementing parameters for this function is actually the crux of this entire problem.
IUnknown::Release only expects one
ByVal argument, the instance pointer. We now want a function with completely different parameters to be called in place of
There are certain implications that come with this. During the automatic
Release call, all of the parameters now defined point to the memory occupied by the
ByVal instancePtr argument, or the memory next to it on the stack frame.
Because the parameters now essentially point to memory having nothing to do with their type, accessing them in any way assuming their type will crash the Application. This of course includes copying them, the reason why the parameters must be
Of course, we'd like to use the arguments inside the
Sub. In order to do this, the
Sub must have a way of "knowing" it was called as the fake
Release method and exit before accessing any of the arguments. This can be achieved by examining the memory passed to the optional parameters when called as
Release. A way to do this is as follows:
Debug.Print "Pointer of the object:" Debug.Print VarPtr(o) '<-- VarPtr of the Static object (instancePtr!) Debug.Print "Memory passed into parameters:" Debug.Print VarPtr(Range) '<-- First parameter Debug.Print VarPtr(refreshTimeSeconds) Debug.Print VarPtr(schedule) '<-- instancePtr on x64 Debug.Print VarPtr(stopAll) '<-- instancePtr on x86 Debug.Print VarPtr(param5) Debug.Print VarPtr(param6) Debug.Print VarPtr(param7) Debug.Print VarPtr(param8)
Note that accessing the variables like this does not assume their type, doesn't lead to a
QueryInterface call, and hence doesn't crash the application.
Running this test will reveal, that the actual memory containing
instancePtr seems to align with the third parameter
schedule in 64-bit and the fourth parameter
stopAll in 32-bit applications.
The memory before that (parameter 1 and 2) seems to be something related to the implementation of the
ByVal argument, and the memory after seems to be
Null followed by random stuff.
Interestingly, the equality of e.g.
VarPtr(schedule) = VarPtr(o) on x64, does only hold if
o is the first
Static variable declared in the
Sub. Declaring other
Static variables before will create an offset between
VarPtr(o) and VarPtr(schedule) equal to the number of bytes declared before
o is not declared
Static but just regular with
Dim, there seems to be a base offset of 8 between the two values
VarPtr(o), and in this case, every regularly
Dim'd variable before
o seems to create an offset in the other direction than in the
My testing seems to suggest, however, that as long as
Static and declared as the first variable, there is no offset and the equality does hold. This can be used to recognize that the
Sub was called as the
Release method and therefore we can exit the
Sub before actually accessing any of the parameters.
Because I have little understanding of the underlying workings of VBA, I'm interested in how all of these observed behaviors can be explained.
I'd like to know if using this admittedly quite hacky technique bears too many risks or if, henceforth, I can assume it is reliable enough to actually be used.
Are there any risks of having the
ByRef parameters point to random memory as long as it can be assured that they will not be touched while doing so?
Does the condition I'm using to detect the
Release call hold reliably or was I just "lucky"?