How to make safe API Timers in VBA?
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
Solution 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:
- 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.
Solution 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).
Solution 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.
Solution 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.