How to make safe API Timers in VBA?

Pointer-Safe and 64-Bit declarations for the Windows Timer API in VBA:

As promised, here are the 32-Bit and 64-Bit API declarations for the Timer API, using LongLong and the Safe Pointer type:

Option Explicit
Option Private Module

#If VBA7 And Win64 Then    ' 64 bit Excel under 64-bit windows
                           ' Use LongLong and LongPtr

    Private Declare PtrSafe Function SetTimer Lib "user32" _
                                    (ByVal hwnd As LongPtr, _
                                     ByVal nIDEvent As LongPtr, _
                                     ByVal uElapse As LongLong, _
                                     ByVal lpTimerFunc As LongPtr _
                                     ) As Long

    Public Declare PtrSafe Function KillTimer Lib "user32" _
                                    (ByVal hwnd As LongPtr, _
                                     ByVal nIDEvent As LongPtr _
                                     ) As Long
    Public TimerID As LongPtr
    
#ElseIf VBA7 Then     ' 64 bit Excel in all environments 
                      ' Use LongPtr only, LongLong is not available

    Private Declare PtrSafe Function SetTimer Lib "user32" _
                                    (ByVal hwnd As LongPtr, _
                                     ByVal nIDEvent As Long, _
                                     ByVal uElapse As Long, _
                                     ByVal lpTimerFunc As LongPtr) As Long

    Private Declare PtrSafe Function KillTimer Lib "user32" _
                                    (ByVal hwnd As LongPtr, _
                                     ByVal nIDEvent As Long) As Long

    Public TimerID As LongPtr

#Else    ' 32 bit Excel

    Private Declare Function SetTimer Lib "user32" _
                            (ByVal hwnd As Long, _
                             ByVal nIDEvent As Long, _
                             ByVal uElapse As Long, _
                             ByVal lpTimerFunc As Long) As Long

    Public Declare Function KillTimer Lib "user32" _
                            (ByVal hwnd As Long, _
                             ByVal nIDEvent As Long) As Long

    Public TimerID As Long

#End If



' Call the timer as: 
'    SetTimer 0&, 0&, lngMilliseconds, AddressOf TimerProc



#If VBA7 And Win64 Then     ' 64 bit Excel under 64-bit windows  ' Use LongLong and LongPtr
                            ' Note that wMsg is always the WM_TIMER message, which actually fits in a Long

    Public Sub TimerProc(ByVal hwnd As LongPtr, _
                         ByVal wMsg As LongLong, _
                         ByVal idEvent As LongPtr, _
                         ByVal dwTime As LongLong)
    On Error Resume Next

    KillTimer hwnd, idEvent   ' Kill the recurring callback here, if that's what you want to do
                              ' Otherwise, implement a lobal KillTimer call on exit

    ' **** YOUR TIMER PROCESS GOES HERE **** 
  
    End Sub
    
#ElseIf VBA7 Then          ' 64 bit Excel in all environments   
                        ' Use LongPtr only

    Public Sub TimerProc(ByVal hwnd As LongPtr, _
                         ByVal wMsg As Long, _
                         ByVal idEvent As LongPtr, _
                         ByVal dwTime As Long)
    On Error Resume Next

    KillTimer hwnd, idEvent   ' Kill the recurring callback here, if that's what you want to do
                              ' Otherwise, implement a lobal KillTimer call on exit

    ' **** YOUR TIMER PROCESS GOES HERE **** 
        
    End Sub
    
#Else    ' 32 bit Excel

    Public Sub TimerProcInputBox(ByVal hwnd As Long, _
                                 ByVal wMsg As Long, _
                                 ByVal idEvent As Long, _
                                 ByVal dwTime As Long)
    On Error Resume Next

    KillTimer hwnd, idEvent   ' Kill the recurring callback here, if that's what you want to do
                              ' Otherwise, implement a lobal KillTimer call on exit

    ' **** YOUR TIMER PROCESS GOES HERE **** 

    End Sub

#End If

The hwnd parameter is set to zero in the sample code above, and should always will be zero if you're calling this from VBA instead of associating the call with (say) an InputBox or form.

A fully-worked example of this Timer API, including the use of the hwnd parameter for a window, is available on the Excellerando website:

Using the VBA InputBox for passwords and hiding the user's keyboard input with asterisks.

**Footnote:**

This has been published as a separate reply to my explanation of the system errors associated with calling the Timer API without careful error-handling: it's a separate topic, and StackOverflow will benefit from a separate and searchable answer with the Pointer-Safe and 64-Bit declarations for the Windows Timer API.

There are bad examples of the API declarations out there on the web; and there are very few examples for the common case of VBA7 (which supports the Safe Pointer type) installed on a 32-Bit Windows environment (which doesn't support the 64-Bit 'LongLong' integer).

@CoolBlue: And what is the mechanism of the crash: what is happening exactly to make Excel crash?

I can can give you an expansion of Siddarth Rout's answer, but not a complete explanation.

API calls are not VBA: they exist outside VBA's error-handlers and when things go wrong they will either do nothing, or call on a resource in memory that doesn't exist, or attempt to read (or write!) to memory that's outside the designated memory space for Excel.exe

When that happens, the Operating System will step in and shut your application down. We used to call this a 'General Protection Fault' and that's still a useful description of the process.

Now for some details.

When you call a function in VBA, you just write the name - let's call it 'CheckMyFile()' - and that's all you need to know within VBA. If there's nothing called 'CheckMyFile' to call, or it's declared where your call can't see it, the compiler or the runtime engine will raise an error in the form of a breakpoint, or a warning before it compiles and runs.

Behind the scenes, there's a numeric address associated with the string 'CheckMyFile': I'm simplifying a bit, but we refer to that address as a Function Pointer - follow that address, and we get to a structured block of memory that stores definitions of the function parameters, space for their stored values and, behind that, addresses directing those parameters into the functional structures created to execute your VBA and return values to the address for the function's output.

Things can go wrong, and VBA does a lot of work to ensure that all this folds up gracefully when they do go wrong.

If you give that function pointer to something that isn't VBA - an external application or (say) an API Timer Call - your function can still be called, it can still run, and everything will work.

We refer to this as a 'Callback' when you hand the function pointer to the API, because you call its timer function, and it calls you back.

But there had better be a valid function behind that pointer.

If there isn't, the external application will call its own error-handlers, and they won't be as forgiving as VBA.

It might just drop the call and do nothing if Excel and VBA are in a 'busy' state or otherwise unavailable when it tries to use that function pointer: you might be lucky, just that once. But it might call down the wrath of the operating system on the Excel.exe process.

If the callback results in an error, and that error isn't handled by your code, VBA will raise the error to the caller - and, as the caller isn't VBA, it'll probably have no way of handling that: and it'll call for 'help' from the operation system.

If it's an API call, it was written for developers who 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.

With an API callback, caller is the operating system, and its response to detecting an error will be to shut you down.

So that's a very simple outline of the process - a 'why' rather than a 'what' explanation of it.

The full explanation, without the oversimplifications, is for C++ developers. If you really want the answer in depth, you must learn to program with pointers; and you must become fluent with the concepts and practice of memory allocation, exceptions, the consequences of a bad pointer and the mechanisms used by an operating system to manage running applications and detect an invalid operation.

VBA exists to shield you from that knowledge and simplify the task of writing applications.

I read in various places that API timers are risky in VBA

Well the statement should be I read in various places that API timers are risky? And the reason why I say that is because these APIs can be use in VB6/VBA/VB.Net etc..

So are they risky? Yup they are but then so is tight rope walking. One false move and you are done. And this is not the case with just SetTimer API but with almost any API.

I created an example way back in 2009 which uses SetTimer API to create splash screens in Excel. Here is the LINK.

Now if you extract the files and you directly open the excel file then you will see that Excel Crashes. To make it work, press the SHIFT key and then open Excel so that the macros don't run. Next change the path of the images. The new path would be the path of the images that you extracted from the zip file. once you change the path, simply save and close the file. Next time when you run it, Excel won't crash.

Here is the code in the Excel file

Public Declare Function SetTimer Lib "user32" ( _
ByVal HWnd As Long, ByVal nIDEvent As Long, _
ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long

Public Declare Function KillTimer Lib "user32" ( _
ByVal HWnd As Long, ByVal nIDEvent As Long) As Long

Public TimerID As Long, TimerSeconds As Single, tim As Boolean
Dim Counter As Long
Sub StartTimer()
    '~~ Set the timer.
    TimerSeconds = 1
    TimerID = SetTimer(0&, 0&, TimerSeconds * 1000&, AddressOf TimerProc)
End Sub

Sub EndTimer()
    On Error Resume Next
    KillTimer 0&, TimerID
End Sub

Sub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, _
ByVal nIDEvent As Long, ByVal dwTimer As Long)
    If tim = False Then
        UserForm1.Image1.Picture = LoadPicture("C:\temp\1.bmp")
        tim = True
    Else
        UserForm1.Image1.Picture = LoadPicture("C:\temp\2.bmp")
        tim = False
    End If
    Counter = Counter + 1
    If Counter = 10 Then
        EndTimer
        Unload UserForm1
    End If
End Sub

When is the API timer safe and when is it not? Are there some broad principles to help me understand?

So it all boils down to one fact. How robust is your code. If your code handles every scenario, then the SetTimer API or as a matter of fact any API will not fail.

How to make safe API Timers in VBA?

Pointer-Safe and 64-Bit declarations for the Windows Timer API in VBA:

Tags:

Timer

Excel

Vba

Related

Recent Posts