Too long for a comment on the question that prompted this, so I'll ask here.
Is it safe (at least from the problems I talk about below) to use callback function pointers in VBA code; given its slightly unusual relationship to the host application and the quirks of the interpreter?
This question about using WinAPI to register callbacks discusses potential pitfalls which lead to crashing when the OS invokes the callback function. Specifically, it asks why interacting with the worksheet occasionally leads to problems in the TIMERPROC callback. My immediate thought upon reading the description was that the Excel Object Model doesn't work well with asynchronous programming.
The question includes an extract of code which "works", I won't include it here though because I think this answer to a related question actually illustrates it much more succinctly:
I was able to solve it with
On Error Resume Next[...]
Sub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, ByVal nIDEvent As Long, ByVal dwTimer As Long) On Error GoTo BeforeExit Range("A1") = "test" BeforeExit: End Sub
...that confirmed my idea that the problem was probably coming from interacting with the object model (Range("A1") = "test") asynchronously whilst it was in some locked state, and could be solved by catching the error with OERN.
In that original question, @NigelHeffernan gave a great answer about what might be causing a crash when Windows invokes the callback function, which he chalks down to 3 reasons:
[developers using the API] are assumed to have put the error-handling and contingency management in place in the calling code.
Those assumptions are:
- There will definitely be a valid function behind that pointer;
- It definitely be available when it is called;
- ...And it will raise no errors to the caller.
Point 3 is (I think) most relevant to the specific question, and what I assume to be the cause of OP's crashes.
What I'm concerned about is that some of the comments (1,2) imply (to my mind at least) that point 2 might be to blame; that Excel being in "edit mode" somehow suspends VBA code from running and means the OS can't run the callback, but instead gets an "Excel is busy" error.
I know that VBA code often runs in the host's main/UI thread, which means that if Excel is busy running other UI stuff then VBA can't execute. However the message loop which reads the WM_TIMER message and ultimately runs the callback necessarily lives in the same thread as my VBA code; so the callback won't be called when Excel is actually busy processing other UI messages. Sure the Excel Object Model may be in some state that means trying to access it will raise errors, but then the issue is raising an error to the caller, not that the callback is unavailable.
Meanwhile another comment from the OP and some explanation given later on imply that their problem was actually the third factor (point 1 - the function pointer was invalid). What they've written seems to suggest that the TimerProc behind the function pointer is being Garbage-Collected and invalidated at some point during execution, OERN keeps it alive by holding a reference to it.
This whole idea seems really weird to me though; functions are not 1st class citizens in VBA and I don't think they follow COM reference counting (what would hold the reference to them anyway, a pointer is just a number not a reference?). I assumed that function pointers were set at compile-time, and they seem to remain valid even between End statements and reset buttons, so I'm not sure what would make them go out of scope as OP suggests. I also think that if they did it would have consequences for VBA code in general, not just when using APIs. So I'm not sure whether this is actually a problem to worry about.
TL;DR
When using callbacks in asynchronous single threaded code (such as with the SetTimer API), is there any risk of:
- Callbacks falling out of scope between calls so that the function pointers provided to APIs become invalid
- Excel/ the host application being in a "busy state" when the callbacks are invoked so that the calls fail
I can imagine why these might be problems across threads, but I thought the whole message queue system that Windows is built on would prevent this from being a problem for single threaded VBA code.
Or in other words, am I right in thinking that:
- The host application should have no influence on the "availability" of VBA code on that thread
- There is no reason for a function pointer to become invalid (un-callable) after I hit F5 - except perhaps for some manual memory mischief
Oh and please do read those comments in context, it's perfectly possible that I've totally misinterpreted them:) (I don't have much experience in this area at all)
