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