Sub that calls itself when its parent Workbook is closed

Question

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 IUnknown::Release

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 Application.OnTime.

Both of these issues have to do with how to cancel a task scheduled with Application.OnTime.

1. 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 Range and 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.

2. Application.OnTime will reopen a closed Workbook if a macro was scheduled to run in it!

There seem to be two options to deal with this:

1. The user stops the loop manually before closing. This is very inconvenient and not really viable for most people.
2. The user adds code to the Workbook_BeforeClose event. 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 ThisWorkbook module 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.

Firstly, the 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 IUnknown::Release trick.

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 Release method.

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:

1. IUnknown::AddRef, called whenever the instances reference counter is incremented
2. IUnknown::QueryInterface, called whenever information about the object's interface is required
3. 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:

1. When the function containing it gets recompiled. (Excel will crash)
2. When the Stop button in the VBA-IDE is pressed.
3. 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 IUnknown::Release

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 Application.OnTime and 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.

The following 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 Release.

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 ByRef.

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.

If o is not declared Static but just regular with Dim, there seems to be a base offset of 8 between the two values VarPtr(schedule) and 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 Static case.

My testing seems to suggest, however, that as long as o is 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"?

Answer 1

Unfortunately, the Release magic trick is dangerous because it can mess with the underlying EbMode function used by VB to determine if code is running or not.

To replicate, run Main as presented in the question under the Option 3, the "magic" way section. Here is the same code for convenience:

Option Explicit

#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

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

After running Main run any method and reset state while code is still running. Here are 2 ways:

1. Run this:

Sub Boom()
    End
End Sub

Or:

2. Run this:

Sub Boom2()
    Stop
End Sub

and when the code breaks on the Stop line, press the Reset button in the IDE:

Although, state was lost, the Locals window will show "Running":

and now VBA has gone 'crazy' as there is no way to step through code anymore or run other macros until the whole Application is restarted.

---

In short, using this trick directly or via the Sub that calls itself is:

• safe, if state is lost while no code is running
• unsafe, if state is lost while code is running and Application needs restarting

---

Alternatively, I've created the StateLossCallback class that is:

• safe, if state is lost while no code is running
• safe, but does nothing (callback not called), if state is lost while code is running

Answer 2

Alternative suggestion too long for a comment. I wrote code to use WinAPI timers: Tick. Tick. *breathe* BOOM! - Setting up real, stable asynchronous callbacks with WinAPI Timers in VBA

These can be used to call a method which may be:

• Stopped with the stop button
• Disabled when Excel closes
• Even stopped with the End statement
• Can handle Errors and launch debugger (just make sure to continue execution after)

Standard Module

Option Explicit

Sub StartContinuousRecalculating()
    Dim cellToRecalc As Range
    Set cellToRecalc = Sheet1.Range("A1")
    'spawn a new timer instance, it will call "RecalculateCellProc" supplying the Data argument as the userData param
    ' i.e. it will forward the cell reference to the proc every time it is called
    TickerAPI.StartManagedTimer New RecalculateCellProc, delaymillis:=1000, Data:=cellToRecalc
End Sub

Class RecalculateCellProc

Option Explicit

Implements Timing.ITimerProc

Private Sub ITimerProc_Exec(ByVal timerID As LongPtr, ByVal userData As Variant, ByVal tickCount As Long)
    'Doesn't matter if we raise errors here as this is a managed timer proc, error details are logged
    'Can even set breakpoints as long as we don't click `End` during a callback, that will crash Excel
    userData.Calculate 'assume it's the range we're expecting
End Sub

Obviously this isn't commentary on your approach. You just may want to do it this way since it is a bit less hacky (e.g. allows breakpoints/errors, doesn't use state machine logic that's hard to follow), and you can have finer grained control over what you call and millisecond delays.

---

CristianBuse created another great approach to these timers using 1 workbook that spawns another delegate workbook to handle the WinAPI calls with an extra degree of safety. His approach is even more stable/harder to crash - you have to set a breakpoint in the second workbook I think. But it's a bit more overhead (which may or may not be an issue for you) and Excel specific IIUC and it sounds like you want host-agnostic code.