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
@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:
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With