How to make safe API Timers in VBA?

Question

I read in various places that API timers are risky in VBA, that if you edit a cell while the timer is running it will crash Excel.

This code from http://optionexplicitvba.wordpress.com written by Jordan Goldmeier does not seem to have this problem. It fades a pop-up using the timer and while its fading, I can click and enter text in cells and the formula bar.

When is the API timer safe and when is it not? Are there some specific principles to help me understand? And what is the mechanism of the crash: what is happening exactly to make Excel crash?

Option Explicit
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
Public TimerSeconds As Single
Public bTimerEnabled As Boolean
Public iCounter As Integer
Public bComplete As Boolean

Public EventType As Integer

Public Sub Reset()
    With Sheet1.Shapes("MyLabel")
        .Fill.Transparency = 0
        .Line.Transparency = 0
        .TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(0, 0, 0)
    End With
    Sheet1.Shapes("MyLabel").Visible = msoTrue
End Sub

Sub StartTimer()
    iCounter = 1
    Reset
    TimerID = SetTimer(0&, 0&, 0.05 * 1000&, AddressOf TimerProc)
End Sub

Sub EndTimer()
    KillTimer 0&, TimerID
    bTimerEnabled = False
    bComplete = True
End Sub

Sub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, _
    ByVal nIDEvent As Long, ByVal dwTimer As Long)

    On Error Resume Next

    Debug.Print iCounter
    If iCounter > 50 Then
        With Sheet1.Shapes("MyLabel")
            .Fill.Transparency = (iCounter - 50) / 50
            .Line.Transparency = (iCounter - 50) / 50
            .TextFrame2.TextRange.Font.Fill.ForeColor.RGB = _
                RGB((iCounter - 50) / 50 * 224, _
                     (iCounter - 50) / 50 * 224, _
                     (iCounter - 50) / 50 * 224)
        End With
    End If

    If iCounter > 100 Then
        Sheet1.Shapes("MyLabel").Visible = msoFalse
        EndTimer
    End If

    iCounter = iCounter + 1
End Sub

Public Function ShowPopup(index As Integer)

    Sheet1.Range("Hotzone.Index").Value = index

    iCounter = 1

    If bTimerEnabled = False Then
        StartTimer
        bTimerEnabled = True
        Reset
    Else
        Reset
    End If

    With Sheet1.Shapes("MyLabel")
        .Left = Sheet1.Range("Hotzones").Cells(index, 1).Left + _
            Sheet1.Range("Hotzones").Cells(index, 1).Width
        .Top = Sheet1.Range("Hotzones").Cells(index, 1).Top - _
                (.Height / 2)
    End With
    Sheet1.Range("a4:a6").Cells(index, 1).Value = index

End Function

Answer 1

@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:

1. There will definitely be a valid function behind that pointer;
2. It definitely be available when it is called;
3. ...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.

Answer 2

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

Answer 3

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.

Answer 4

@CoolBlue I wrote the code you posted above. It's true that APIs can act unpredictably, at least compared to normal code. However, if your code is robust enough (following @Siddharth Rout's comments from above), then it's no longer a prediction. In fact, that unpredictability comes in during development.

For example, in my first iteration of the rollover popup created above, I had accidentally typed KillTimer in the IF statement. Basically, where EndTimer exists now I had written KillTimer. I did this without thinking. I knew I had a procedure that would end the timer, but I momentarily confused EndTimer with KillTimer.

So here's why I bring this up: typically, when you make this type of mistake in Excel, you'd receive a runtime error. However, because you are working with APIs, you just get an illegal error, and the entire Excel application becomes unresponsive and quits. So, if you haven't saved before starting the timer, you lose everything (which is essentially what happened to me the first time through). Worse, because you don't receive a runtime error, you won't know immediately which line caused the error. In a project like this, you have to expect several illegal errors (and subsequent reloading of Excel) to diagnose the error. It can be a painful process, sometimes. But this is a typical debugging situation that happens when you worki with APIs. That the errors are not highlighted directly - and illegal errors appear to happen at random - are why many have described APIs as unpredictable and risky.

But they're not risky, so long as you can find and diagnose errors. In my code above, I believe I've created an essentially closed form solution. There aren't any errors someone could introduce that would cause a problem later. (Don't take that as a challenge folks.)

And just to give you some specific guidelines to avoid errors:

• If you start a timer, ensure you kill it later. If you have an Excel runtime error before the timer is killed, it could go on forever and eat your memory. Use the console (Debug.Print) to write a line every time the TimerProc is called. If it keeps ticking away in you console even after your code is done executing, then you have a runaway timer. Quit Excel and come back in when this happens.
• Don't use multiple timers. Use ONE timer to handle multiple timing elements.
• Don't start a new timer without killing an old one.
• Most important: test on your friend's computer to ensure it works across different platforms.

Also, just to be clear: there's no problem using the API timer and editing a cell at the same time. There's nothing about Timers that will preclude your ability to edit anything on the sheet.

Answer 5

I have also been faced with the fact that Excel crashes while entering a value and found this contribution. Great! My problem was solved as soon I added this line:

On Error Resume Next 

to "TimerProc".

Answer 6

With the API timer, as soon as I set the time lapse too short, Excel would crash because it had not finished the previous timed task before the next was scheduled. This does not happen with ontime because you set ontime after finishing TimerProc.

Maybe it is possible to kill the timer first thing in Timerproc, and set a new one just before finishing.

You should be aware that Killtimer actually fails at times, leaving the timer alive and continuing to call the procedure forever. So a machine gun code with feedback control is necessary to be sure it is really dead.

//pseudo code :
start_kill = timer()
While still_alive= (negative result of killtimer) do
Still_ailve = KillTimer TimerID.
If timer - start_kill > 10 then msgbox "not dead     find a bigger gun" Exit sub
Wend

Of course you need a time out to exit this loop.